Check values match range

[guscrouchend]

hi,

I would like to check that all values in A1:A400 match one of the values in C1:C54, otherwsie they should be turned RED or highlighted or something.

I would to run this as a process, rather than use conditional formatting.

Would be very grateful for someone's assistance.

thanks

gus

 

[Matt Rogers]

Hi.

Try this macro:

Sub compare()
Dim lngI As Long, intValue As Integer
For lngI = 1 To Cells(Rows.Count, 1).End(xlUp).Row
intValue = Application.WorksheetFunction. _
CountIf(Worksheets("Sheet1").Range("C1:C54"), Cells(lngI, 1).Value) 'your sheetname
If intValue > 0 Then
Cells(lngI, 1).Interior.ColorIndex = 3
End If
Next lngI
End Sub