Can't set font.colorindex from VBA
Yes, I intended to have the text in column I set. I tried in in another
workbook, and it worked as expected, so it must be a problem with the
original workbook. Nothing's protected, so I don't know what the problem is.
The problem wrkbk has dde links to an external data source, and when I
opened it I told it NOT to update. But I said Allow macros, so.... Anyway,
now begins the fun process of figuring out why it's not working.
Sorry to waste your time.
Thanks again
Mike J
"Bob Phillips" wrote:
It works for me. You do know that it sets the colour in column I don't you?
--
HTH
Bob Phillips
"Mike Jamesson" wrote in message
...
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
|