Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Error setting Font.ColorIndex
I've got this very simplistic idea that, when a user changes the text in a
cell, I want to change the colour of the text so they can keep track of what has been changed and what hasn't. This all seemed very simple, and I ended up with code like this: Private Sub Worksheet_Change(ByVal Target As Range) Target.Font.ColorIndex = 3 End Sub Very simple indeed. However, it doesn't work. When I come to set the ColorIndex, the macro fails with an error 1004, "Unable to set the ColorIndex property of the Font class" Having failed at that, I tried changing other properties, such as the Color and Bold properties. All of these failed with the same error. Irritating thing is, the help file shows an example of setting the ColorIndex of a Font object, so it must be possible to do. Anyone know why this wouldn't work? Thanks Steve Office 2003/Excel 2003, Windows/XP SP2 (pro and home) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Error setting Font.ColorIndex
Works fine here. You have stored it in the worksheet module haven't you.
-- HTH Bob Phillips "Steve Barnett" wrote in message ... I've got this very simplistic idea that, when a user changes the text in a cell, I want to change the colour of the text so they can keep track of what has been changed and what hasn't. This all seemed very simple, and I ended up with code like this: Private Sub Worksheet_Change(ByVal Target As Range) Target.Font.ColorIndex = 3 End Sub Very simple indeed. However, it doesn't work. When I come to set the ColorIndex, the macro fails with an error 1004, "Unable to set the ColorIndex property of the Font class" Having failed at that, I tried changing other properties, such as the Color and Bold properties. All of these failed with the same error. Irritating thing is, the help file shows an example of setting the ColorIndex of a Font object, so it must be possible to do. Anyone know why this wouldn't work? Thanks Steve Office 2003/Excel 2003, Windows/XP SP2 (pro and home) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Error setting Font.ColorIndex
Yes.
Had another play - and realised that the sheet I was modifying was protected (It's not my spreadsheet - I'm doing someone a favour - will I never learn!) I modified the code to unprotect the sheet, change the property and re-protect it and everything is now working fine. Thanks Steve "Bob Phillips" wrote in message ... Works fine here. You have stored it in the worksheet module haven't you. -- HTH Bob Phillips "Steve Barnett" wrote in message ... I've got this very simplistic idea that, when a user changes the text in a cell, I want to change the colour of the text so they can keep track of what has been changed and what hasn't. This all seemed very simple, and I ended up with code like this: Private Sub Worksheet_Change(ByVal Target As Range) Target.Font.ColorIndex = 3 End Sub Very simple indeed. However, it doesn't work. When I come to set the ColorIndex, the macro fails with an error 1004, "Unable to set the ColorIndex property of the Font class" Having failed at that, I tried changing other properties, such as the Color and Bold properties. All of these failed with the same error. Irritating thing is, the help file shows an example of setting the ColorIndex of a Font object, so it must be possible to do. Anyone know why this wouldn't work? Thanks Steve Office 2003/Excel 2003, Windows/XP SP2 (pro and home) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Error setting Font.ColorIndex
Maybe it is a protected sheet?
DM Unseen |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Error setting Font.ColorIndex
That's odd. I thought of that and tried with a protected sheet, but got
nowhere near that error! -- HTH Bob Phillips "Steve Barnett" wrote in message ... Yes. Had another play - and realised that the sheet I was modifying was protected (It's not my spreadsheet - I'm doing someone a favour - will I never learn!) I modified the code to unprotect the sheet, change the property and re-protect it and everything is now working fine. Thanks Steve "Bob Phillips" wrote in message ... Works fine here. You have stored it in the worksheet module haven't you. -- HTH Bob Phillips "Steve Barnett" wrote in message ... I've got this very simplistic idea that, when a user changes the text in a cell, I want to change the colour of the text so they can keep track of what has been changed and what hasn't. This all seemed very simple, and I ended up with code like this: Private Sub Worksheet_Change(ByVal Target As Range) Target.Font.ColorIndex = 3 End Sub Very simple indeed. However, it doesn't work. When I come to set the ColorIndex, the macro fails with an error 1004, "Unable to set the ColorIndex property of the Font class" Having failed at that, I tried changing other properties, such as the Color and Bold properties. All of these failed with the same error. Irritating thing is, the help file shows an example of setting the ColorIndex of a Font object, so it must be possible to do. Anyone know why this wouldn't work? Thanks Steve Office 2003/Excel 2003, Windows/XP SP2 (pro and home) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If Font.ColorIndex = 5 then . . . | Excel Programming | |||
VBA syntax for Font & Interior ColorIndex | Excel Discussion (Misc queries) | |||
EXCEL VB Macro Run Time Error 1004 - Setting ColorIndex | Excel Programming | |||
unable to set the colorindex property of the font class | Excel Programming | |||
Font and Fill ColorIndex | Excel Programming |