How to Highlight Value if not in array

[Nitesh Khot]

I want to highlight cell value if not present in array..

ie. if in array("IN","US","SG")

If in Range(A2) to last row if cell value not in array then highlight cell as red backcolor.

Thanks in advance,
Nikh

 

[PCosta87]

Hi @Nitesh Khot

I believe this will do:

Sub test()

    Dim array_test(2)
    Dim c As Range
   
    array_test(0) = "IN"
    array_test(1) = "US"
    array_test(2) = "SG"
   
    For Each c In Range("A2:A" & Range("A2").End(xlDown).Row).Cells
        If IsError(Application.Match(c.Value, array_test, False)) Then
            c.Interior.ColorIndex = 3
        End If
    Next c

End Sub

Hope this helps

Regards

 

[Nitesh Khot]

Awesome..

 

[PCosta87]

I'm glad it was useful