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

Can't set font.colorindex from VBA
 

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

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

Can't set font.colorindex from VBA
 

Mike,

Don't use DDE myself, but I believe that a DDE update doesn't trigger the
Change event.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Mike Jamesson" wrote in message
...
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

Can't set font.colorindex from VBA
 

Bob,

Yeah, the DDE guess was a shot in the dark, and a "miss" at that. Found the
problem. I had copied a function from an excel website and put it in a
module, and this function included the line
Application.Volatile True
I had never seen this before, but it certainly looked like a suspect of
something that might interfere with events. So I commented it out, and my
Sub worked as expected. Final detail: this function that included
Application.Volatile True was being called from a cell in the sheet. When I
cleared that cell, my code also worked properly, even with
Application.Volatile True NOT commented out. Here's the function that gave
me the problem, fwiw:

Function CellColorIndex(InRange As Range, Optional _
OfText As Boolean = False) As Integer
'
' This function returns the ColorIndex value of a the Interior
' (background) of a cell, or, if OfText is true, of the Font in the cell.
'
Application.Volatile True
If OfText = True Then
CellColorIndex = InRange(1, 1).Font.ColorIndex
Else
CellColorIndex = InRange(1, 1).Interior.ColorIndex
End If

End Function

Personally, I'd call this a bug.

Thanks again for your input.

Mike J


"Bob Phillips" wrote:

Mike,

Don't use DDE myself, but I believe that a DDE update doesn't trigger the
Change event.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Mike Jamesson" wrote in message
...
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