Can't set font.colorindex from VBA
Problem: have a Worksheet_Change event handler intented to change the text
color of a nearby cell when a cell in column 8 is changed. Even tho the code
is run, and the line that sets the nearby cell's colorindex is executed,
nothing happens to the text in the cell. Here's the code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 8 Then
If Target.Value = "EUR" Then
Target.Offset(0, 1).Font.ColorIndex = 5
ElseIf Target.Value = "BPS" Then
Target.Offset(0, 1).Font.ColorIndex = 10
ElseIf Target.Value = "SEK" Then
Target.Offset(0, 1).Font.ColorIndex = 46
End If
End If
End Sub
I have set breakpoints on the lines
Target.Offset(0, 1).Font.ColorIndex = 5
Target.Offset(0, 1).Font.ColorIndex = 10
Target.Offset(0, 1).Font.ColorIndex = 46
and sure enough, when the conditions are met, the code stops on those lines
as it should since I put breakpoints on them. And when I click run, the line
executes, but the value of Target.Offset(0, 1).Font.ColorIndex stays at
-4105, which I guess means black, or at least the default.
It's as if Font.ColorIndex is read-only. Is that the case? If not, what
am I doing wrong? If so, how do I achieve my goal?
Thanks in advance!
Mike J
|