Color Entire Row Based on Cell Value
Does anyone know what could be causing this error?
From the MSDN help on the
The ColorIndex property can have valid integer arguments between 0 and 56 that generate color. However, you can assign decimal or string values to the property without generating a run-time error. In these instances, Excel tries to randomly apply a color that corresponds to the argument value. However, setting the property to an integer value outside the range of 0 to 56 causes the following error:
Runtime Error '9': Subscript out of range
You can find the Color Palette with the valid indices on the same page:
ColorIndex is different than
Color, which uses the RGB specification and is more versatile. More info on
I personally prefer using
Color and the built-in VBA Color Enumerations
vbBlack, vbRed, vbGreen, vbYellow, vbBlue, vbMagenta, vbCyan, and
vbWhite. For most applications these are enough, but if more colors are necessary then using a custom enumeration color is also possible, and more elegant than looking up the RGB tables..
I hope this helps!