text&formula coloring!

  • Thread starter Thread starter riccifs
  • Start date Start date
R

riccifs

Hi to everyone,
does anyone to know how to change the font color of a cell with text
and formula in it?
I mean, If I have ="Hallo "&SUM(A1+5), how can I make Hallo became red
and the result of the formula became blue?
I'm trying with that, it works for a pure text but not for a mixed
text and formula.
Can anyone help me!?

Private Sub Worksheet_Change(ByVal Target As Range)
Set Target = Range("E1")
With Target.Characters(Start:=1, Length:=3).Font
.ColorIndex = 3
End With
With Target.Characters(Start:=4, Length:=15).Font
.ColorIndex = 5
End With
End Sub

Many thanks,
Stefano.
 
If you had stuck to your original posting in the excel.misc group you would see
you have several answers.

In short.......you can't do it.


Gord Dibben MS Excel MVP
 
If you had stuck to your original posting in the excel.misc group you would see
you have several answers.

In short.......you can't do it.

Gord Dibben MS Excel MVP
Click to expand...

I know, sorry for this post, just ignore it.
Many thanks,
Stefano.
 
I don't know exactly what you are trying but the following will put the
string "Hallo" and the contents of Range("A1") + 5 in Range("E1") colored as
you specified.

Sub spltClr()

myStr = "Hallo"
myStr2 = Range("A1") + 5
x = myStr & myStr2
Range("E1") = x
With Range("E1")
.Characters(1, 5).Font.ColorIndex = 3
.Characters(7, Len(ActiveCell) - 5).Font.ColorIndex = 5
End With
End Sub
 
This for the worksheet change:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target = Range("E1") Then
myStr = "Hallo"
myStr2 = Range("A1") + 5
x = myStr & myStr2
Target = x
With Target
.Characters(1, 5).Font.ColorIndex = 3
.Characters(7, Len(ActiveCell) - 5).Font.ColorIndex = 5
End With
End If
End Sub
 
hi
nice. but he doen't have a formula anymore. your sub changes it to hard
text. what happens if the value in A1 changes.
gord's observations holds true. it can be done with text and numbers
formated as text but not with numbers, dates and formulas.

Regards
FSt1
 
Back
Top