I'm trying to color an entire row if two cells in that row have the same value.
For i = 2 To LastRow
If Worksheets("Request Results").Cells(i, 4).Value <> Worksheets("Request Results").Cells(i, 6).Value Then
Cells(i, 1).EnitreRow.Interior.ColorIndex = 255
ElseIf Worksheets("Request Results").Cells(i, 4).Value = Worksheets("Request Results").Cells(i, 6).Value Then
Cells(i, 1).EntireRow.Interior.ColorIndex = 5296274
End If
Next i
The loop goes into the else statement first and I get
"Subscript out of range"
on
Cells(i, 1).EntireRow.Interior.ColorIndex = 5296274
In the formula field, enter the following formula: =$D2>=15. Click the 'Format' button. In the dialog box that opens, set the color in which you want the row to get highlighted. Click OK.
Does anyone know what could be causing this error?
From the MSDN help on the ColorIndex
Property:
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:
Note that ColorIndex
is different than Color
, which uses the RGB specification and is more versatile. More info on Color
vs ColorIndex
here.
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!
change ColorIndex to Color.
Cells(i, 1).EntireRow.Interior.Color = 5296274
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With