Previous Topic Next Topic

Anyone know how to do this?

Closed
Jasmine - Jan 11, 2010 at 03:51 PM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Jan 11, 2010 at 09:04 PM
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 response

Answer 1 / 1
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Jan 11, 2010 at 09:04 PM
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
0