Compare two lists of numbers

[CC268]

See attached sample data. Simply want to highlight numbers in COLUMN B that do not show up in COLUMN A. Would prefer VBA over conditional formatting.

Thanks

18082

[Kenneth Hobs]

Sub Main()
Dim hc&, c As Range, r As Range

Set r = Range("B1", Cells(Rows.Count, "B").End(xlUp))
hc = xlNone 'interior Fill color
r.Interior.ColorIndex = hc

Set r = Range("A1", Cells(Rows.Count, "A").End(xlUp))
hc = vbRed 'interior fill color
For Each c In Range("B1", Cells(Rows.Count, "B").End(xlUp))
If WorksheetFunction.CountIf(r, c) = 0 Then _
c.Interior.Color = hc
Next c
End Sub

[Paul_Hossler]

Probably the simplest way




Option Explicit

'Simply want to highlight numbers in COLUMN B that do not show up in COLUMN A. Would prefer VBA over conditional formatting.
Sub Demo()
Dim rLookHere As Range, rCheck As Range, rCell As Range
Dim i As Long

Application.ScreenUpdating = False

With ActiveSheet
Set rLookHere = Range(.Cells(1, 1), .Cells(1, 1).End(xlDown))
Set rCheck = Range(.Cells(1, 2), .Cells(1, 2).End(xlDown))
End With

For Each rCell In rCheck.Cells
i = -1
On Error Resume Next
i = Application.WorksheetFunction.Match(rCell.Value, rLookHere, 0)
On Error GoTo 0

If i = -1 Then rCell.Interior.Color = vbRed

Next
Application.ScreenUpdating = True
End Sub

[CC268]

Thanks guys!