Highlight Row Based on Cell Value

[jaydee]

Hi guys,

I have a pivot table with one column containing bank locations, and I want to highlight the row if the bank location is a certain number. I only know how to use the OR function in an if statement, but I have a total of 36 different banks that I want to highlight. Could someone help me write a better code?

The code so far looks like


Dim lastrow As Object
Dim irow As Long

Set lastrow = Range("a1").SpecialCells(xlCellTypeLastCell)

irow = lastrow.Row

For r = 1 To irow
If Cells(r, 1).Value = 5 Or Cells(r, 1).Value = 2 Then
Rows(r).EntireRow.Interior.ColorIndex = 35
End If
Next r
End Sub



Thank you in advance.

[Trebor76]

Hi jaydee,

You could use a VLOOKUP either on a separate sheet or directly in the code as I have done with this proposed solution:


Option Explicit

Sub Macro1()

Dim lngMyRow As Long
Dim intMyColourIndex As Integer

For lngMyRow = 1 To Cells(Rows.Count, "A").End(xlUp).Row
On Error Resume Next
intMyColourIndex = Evaluate("VLOOKUP(" & Val(Cells(lngMyRow, 1)) & ",{2,35;5,35},2,0)")
If intMyColourIndex > 0 Then
Rows(lngMyRow).EntireRow.Interior.ColorIndex = intMyColourIndex
intMyColourIndex = 0
End If
On Error GoTo 0
Next lngMyRow

End Sub

Regards,

Robert