Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Color Entire Row Based on Cell Value

Tags:

excel

vba

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
like image 291
user3783788 Avatar asked Jul 28 '14 17:07

user3783788


People also ask

How do you color a whole row based on a cell value?

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.


2 Answers

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:

enter image description here

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!

like image 198
Ioannis Avatar answered Sep 22 '22 14:09

Ioannis


change ColorIndex to Color.

Cells(i, 1).EntireRow.Interior.Color = 5296274
like image 44
Pavel_V Avatar answered Sep 22 '22 14:09

Pavel_V