Anyone know how to do this?

[Closed]
Report
-
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
-
Hello,

I have a spreadsheet with two columns that I need to compare. I need to find all cells in Column A that do not have a match in any cell (B1-B16000) in column B. I would like the cells that have no matches in Column B to be highlighted. See Example Below. 1, 6, and 10 would not be highlighted and 2, 8, 9 would be highlighted to show that they have no match in Column B. Anyone know how to do this?

A B

1 1
2 6
6 10
8 30
9 35
10 37

1 reply

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
802
suppose your data is from row 2 (rows 1 is having column headings
also assume that there is not gap in the data
try this macro. If necessary modify r1 and r2. if this is ok post confiramtion

Sub test()
Dim r1 As Range, c1 As Range, r2 As Range, cfind As Range, x
On Error Resume Next
Set r1 = Range(Range("A2"), Range("A2").End(xlDown))
Set r2 = Range(Range("B2"), Range("B2").End(xlDown))
For Each c1 In r1
x = c1.Value
Set cfind = r2.Cells.Find(what:=x, lookat:=xlWhole)
If cfind Is Nothing Then c1.Interior.ColorIndex = 6
Next c1
End Sub