Multiple Text Colors in Same Cell

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

matthew.nagowski@gmail.com - 11 Jul 2007 16:16 GMT

Hello --

I am wondering if there is a way to format a cell so that multiple
colors can be assigned to different text within the cell.

I am trying to construct a simple in-cell bar chart representing the
distribution of data across different categories, and want to be able
to distinguish across the different categories using colors.

For instance, this would create a two category bar chart, but I have
no way to distinguish between the first category and the second
category:

=REPT(A1,A2)&REPT(A1,B2)

Is there any function (e.g. COLOR() ) that would be able to do the
following:

=COLOR(REPT(A1,A2),SOMECOLOR)&COLOR(REPT(A1,B2),SOMECOLOR)

Maybe there is something in VBA?

Reply to this Message

Dave Peterson - 11 Jul 2007 16:38 GMT

If the cell contains a formula, you can't do this character by character
formatting.

But if you convert it to values, you can select your characters (in the
formulabar) and then format them the way you want.

Could you use multiple lines within the cell:

=REPT(A1,A2)&char(10)&REPT(A1,B2)
(and turn on wrap text)

> Hello --
>
[quoted text clipped - 17 lines]
>
> Maybe there is something in VBA?

Signature

Dave Peterson

Reply to this Message

Brian Withun - 11 Jul 2007 16:44 GMT

You can assign different colors to different areas of text within a
single cell using VBA:

This code puts "Test cell value" into cell B5, then colors the word
"Test" blue (5) and the colors the word "cell" red (3)

Range("B5").Select
ActiveCell.FormulaR1C1 = "Test cell value"
With ActiveCell.Characters(Start:=1, Length:=4).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 5
End With
With ActiveCell.Characters(Start:=5, Length:=1).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
With ActiveCell.Characters(Start:=6, Length:=4).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 3
End With

I hope this helps!

Brian Herbert Withun

Reply to this Message