Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA Error setting Font.ColorIndex | Excel Programming | |||
If Font.ColorIndex = 5 then . . . | Excel Programming | |||
VBA syntax for Font & Interior ColorIndex | Excel Discussion (Misc queries) | |||
unable to set the colorindex property of the font class | Excel Programming | |||
Font and Fill ColorIndex | Excel Programming |