How to change color within string?

[Eric]

Does anyone have any suggestions on how to change color within string?
For example, in cell B1, if(A1=1,"O-F-O-F","O-N-O-N"), I would like to the
last 3 chars into red color, such as O-F and O-N
Does anyone have any suggestions?
Thanks in advance for any suggestions
Eric

 

[Jacob Skaria]

You cannot make a formula to change the formatting.

 

[PBezucha]

Definitely you cannot. But to be positive, there use to be sometimes some
roundabouts. In your simplest case you can make a fake function by means of a
worksheet events procedure in VBA. Try it on an example in a new workbook.
From a worksheet open the project (Alt-F11) and from the Projects window
select MicrosoftExcelObjects and your Worksheet name. By doubleclick open the
codemodule window and paste the following example. For Excel 7 the procedure
has been somewhat changed.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim A As Range, S As String
Set A = Range("A:A")
If Not Intersect(Target, A) Is Nothing Then
If Target.Value > 0 Then S = "AAAAAA" Else S = "BBBBBB"
With Target.Offset(0, 1)
.Value = S
.Characters(Start:=4, Length:=3).Font.ColorIndex = 3
End With
End If
End Sub

Depending on the change in the first column you will get formatted contents
of the adjacent cells.
I hope you can tailor the example to your needs as soon as you will learn
more about events.