Different colors within on cell.

  • Thread starter Thread starter Lee
  • Start date Start date
L

Lee

1 ; 45 ; 0

I want to change the color of each of the above numbers to bold green, 1,
then bold black 45 and finally bold blue 0. Each number in the one cell is
from the following formula: =TEXT(C4,0)&" ; "&TEXT(D4,0)&" ; "&TEXT(E4,0)
It is lack of space on the paper printout that I want to combine the 3 cells
into 1.
Thanks for any help.
Lee
 
It is possible to have the three words in Mom's Apple Pie display in
different colours but there is no way to have a formula deliver three
colours!

How about making the columns narrower?
 
As Bernard wrote, there's no way to do it with formulae. You COULD do it
by replacing the formula with an Event Macro.

One way:

Put this in the worksheet code module (right-click the worksheet tab and
choose View Code):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const csTarget As String = "F4" 'change cell reference to suit
Const csS As String = " ; " 'separator string
Const csP As String = "0" 'TEXT() pattern argument
Dim vClr As Variant
Dim vTxt As Variant
Dim nChr As Long
Dim nLB As Long
Dim nLS As Long
Dim nLen As Long
Dim i As Long

With Range("C4:E4")
If Not Intersect(.Cells, Target) Is Nothing Then
nLS = Len(csS)
vClr = Array(vbGreen, vbBlack, vbBlue)
vTxt = Array(Application.Text(.Cells(1).Value, csP), _
Application.Text(.Cells(2).Value, csP), _
Application.Text(.Cells(3).Value, csP))
nLB = LBound(vTxt)
With Range(csTarget)
With .Font
.Bold = False
.ColorIndex = xlColorIndexAutomatic
End With
.Value = vTxt(nLB) & csS & vTxt(nLB + 1) & _
csS & vTxt(nLB + 2)
nChr = 1
For i = nLB To nLB + 2
nLen = Len(vTxt(i))
With .Characters(nChr, nLen).Font
.Bold = True
.Color = vClr(i)
End With
nChr = nChr + nLen + nLS
Next i
End With
End If
End With
End Sub
 
Thanks for your work. I tried but couldn't get it to work using Excel 2007.
I am not very good with macros but trying to learn. Bernard is correct in
that I can reduce the columns and turn the header text to vertical but I
thought it looked better in one column and I learned some more about what
Excel can and can't do.
Thanks again,
Lee
 
Back
Top