Highlight row based on multiple account numbers in column A

[dstepan]

I've searched this forum and asked Google but can't find what I need.

I have a file with a Trial Balance - 6 columns wide by X number of rows long. The number of rows are dependent on the the accounts utilized that month.

Column a contains an account number in "General" format

I need a code to highlight the rows with the following 15 account numbers:

1112-00
1113-00
1115-00
1116-00
1118-00
1490-00
1491-00
1600-00
2018-00
2090-00
2094-00
3120-00
7001-02
7031-00
7031-02

 

[Yongle]

Sub Highlight()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Dim a, arr, cel As Range, rng As Range
    arr = Split("1112-00 1113-00 1115-00 1116-00 1118-00 1490-00 1491-00 1600-00 2018-00 2090-00 2094-00 3120-00 7001-02 7031-00 7031-02")
    
    With ActiveSheet:   Set rng = .Range("A1", .Range("A" & Rows.Count).End(xlUp)): End With
    rng.Interior.Color = xlNone
    For Each cel In rng
        For Each a In arr
            If a = cel Then
                cel.Interior.Color = RGB(50, 150, 250)
                Exit For
            End If
        Next
    Next cel
    
    Application.Calculation = xlCalculationAutomatic
End Sub

 

[kweaver]

Non-Macro/Conditional Formatting:

I put the list of special A/N in column I (could put anywhere you'd like). Also (assumed) row 1 had headers.

Select the data (e.g., A2:G25) where column A has the Account Numbers.
In Conditional Formatting, use this:

=MATCH($A2,$I$2:$I$16,0)

 

[Yongle]

Being a trial balance, then I guess each account number is found ONCE or not at all
In which case

Sub Highlight()
    Dim a, arr
    arr = Split("1112-00 1113-00 1115-00 1116-00 1118-00 1490-00 1491-00 1600-00 2018-00 2090-00 2094-00 3120-00 7001-02 7031-00 7031-02")
    
    With ActiveSheet.Range("A1", ActiveSheet.Range("A" & Rows.Count).End(xlUp))
        .Interior.Color = xlNone
        On Error Resume Next
        For Each a In arr
            .Find(a, LookIn:=xlValues, lookat:=xlWhole).Interior.Color = RGB(50, 150, 250)
        Next
    End With
End Sub

 

[Yongle]

Conditional formatting as proposed by @kweaver would be my preferred option

 

[dstepan]

I wish I was more VBA savvy to figure out how to highlight the whole row.

I am trying to create Macros/VBA coding so this can be done automatically as the file will rerun each month.

What do I change to highlight columns A:F if a cell matches the specific account numbers?

It is done via Weavers suggestion but I really want a code.

Thanks to Weaver and Yongle. Both suggestions are awesome!

 

[dstepan]

Yongle

I love your code but how do I get the complete row to be highlighted (or at least column A:F) if the cell in column A matches one of the account numbers?

Right now it only copies the cell in column A - not the row.

 

[Yongle]

Two lines to change in post#4 to resize the range to 6 columns

.Resize( , 6).Interior.Color = xlNone

.Find(a, LookIn:=xlValues, lookat:=xlWhole).Resize( , 6 ).Interior.Color = RGB(50, 150, 250)

 

[Lost in Space]

That is beautiful and works like a charm!!!!

I am so glad there are people such as yourself helping the rest of the world!

 

[Yongle]

Glad you have got it working how you wanted
Thanks for the feedback