PDA

View Full Version : [SOLVED] Compare two lists of numbers



CC268
01-19-2017, 02:30 PM
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
01-19-2017, 03:44 PM
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
01-19-2017, 03:49 PM
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
01-19-2017, 03:52 PM
Thanks guys!