CODEWITHSUNDEEP
excelvba
TransRlucent
TransRlucent

Reputation: 13

Highlight Cells that don't match master list in a column

I found this code. I'm looking to error check a group of cells and I'd like to highlight the ones that don't match a Master list.

Dim myRange, v, f, a
Set myRange = Range("P12:P200")
For Each v In Workbooks("Formula_Weighup Audit Auto-Fill Final").Worksheets("Active Master List").Range("J2:J1054")
    Set f = myRange.Find(what:=v, lookat:=xlPart)
    If f Is Nothing Then
        a = f.Address
        Do
            f.Interior.ColorIndex = 5
            Set f = myRange.FindNext
        Loop While f.Address <> a
    End If
Next

MsgBox "Error: R&D #(s) do not exist" & vbNewLine & "(see highlighted items)"
Exit Sub

Upvotes: 0

Views: 397

Answers (1)

BigBen
BigBen

Reputation: 50067

I think something like this is what you're looking for. Note that rangeToCheck, or myRange in your original code, is implicitly on the ActiveSheet.

Sub Test()
    Dim masterWb As Workbook
    Set masterWb = Workbooks("Formula_Weighup Audit Auto-Fill Final.xlsm")
    Dim masterList As Range
    Set masterList = masterWb.Sheets("Active Master List").Range("J2:J1054")

    Dim rangeToCheck As Range
    Set rangeToCheck = Range("P12:P200")

    Dim rng As Range, found As Range

    For Each rng In rangeToCheck
        Set found = masterList.Find(what:=rng.Value, Lookat:=xlPart)
        If found Is Nothing Then rng.Interior.ColorIndex = 5
    Next rng

    MsgBox "Error: R&D #(s) do not exist" & vbNewLine & "(see highlighted items)"

End Sub

EDIT: Based on comments, if you want to Exit Sub when a match is not found, perhaps change

If found is Nothing Then rng.Interior.ColorIndex = 5

to 

If found is Nothing Then
    rng.Interior.ColorIndex = 5
    MsgBox "Error: R&D #(s) do not exist" & vbNewLine & "(see highlighted items)"
    Exit Sub
End If

In regards to your comment, note that IF...Then...Else has both single-line and multiple-line syntax.

Upvotes: 1

Related Questions