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