MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Color Index macro bug


Posted by Herb B. on January 08, 2002 6:49 AM

This is the first part of a repeating macro that test about 45 entries:

Sub TestBusSize()
If Sheets("Audit").Range("H9") > Sheets("Audit").Range("E9") Then
Sheets("Audit").Range("E9").Select
With Selection.Font
.ColorIndex = 3
End With
ElseIf Sheets("Audit").Range("E9") <> "" Then
Sheets("Audit").Range("E9").Select
Selection.Font.ColorIndex = xlNone
End If

I want the macro to color the entry red under the first test and if not, the entry will be the default color or black. I want the macro to change the entry from red back to black again when it is run and the first test is no longer true. It works fine if "H9">"E9" and colors the font red. Otherwise I get the following error message:

"Unable to set the ColorIndex property of the Font class"

Any help on the macro and how to correct this would be appreciated.


Posted by Mudface on January 08, 2002 7:43 AM

Try =xlColorIndexAutomatic rather than =xlNone.