How to change color within string?

  • Thread starter Thread starter Eric
  • Start date Start date
E

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
 
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.
 
Back
Top