text&formula coloring!

[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.

 

[Gord Dibben]

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

 

[riccifs]

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

I know, sorry for this post, just ignore it.
Many thanks,
Stefano.

 

[JLGWhiz]

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

 

[JLGWhiz]

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

 

[FSt1]

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