Highlighting unique values (2003)

Good morning Cram

Something like this seems to work OK. When run it will prompt for a range 1 - highlight your range in column A. When asked for range 2 select the range in column C. The unique items will then be coloured yellow (ColorIndex 6) :

Sub test()
Set a = Application.InputBox("Range 1", , , , , , , 8)
Set b = Application.InputBox("Range 2", , , , , , , 8)
For Each rng1 In a
x = 0
For Each rng2 In b
If rng1.Value = rng2.Value Then x = x + 1
Next rng2
If x = 0 Then rng1.Interior.ColorIndex = 6
Next rng1
For Each rng1 In b
x = 0
For Each rng2 In a
If rng1.Value = rng2.Value Then x = x + 1
Next rng2
If x = 0 Then rng1.Interior.ColorIndex = 6
Next rng1
End Sub

HTH

DominicB

Hi Cram

Originally Posted by Cram

Is ther a way to do it without the input boxes?

Try this updated code. It should detect where your last cell is, and just work on the range. Take note, that it assumes that your sheet works from cell A1 (Cells(1,1)) and cell C1 (Cells(1,3)), which it may not :

Sub test()
Set a = Range(Cells(1, 1), Cells(Range("A65536").End(xlUp).Row, 1))
Set b = Range(Cells(1, 3), Cells(Range("C65536").End(xlUp).Row, 3))
For Each rng1 In a
x = 0
For Each rng2 In b
If rng1.Value = rng2.Value Then x = x + 1
Next rng2
If x = 0 Then rng1.Interior.ColorIndex = 6
Next rng1
For Each rng1 In b
x = 0
For Each rng2 In a
If rng1.Value = rng2.Value Then x = x + 1
Next rng2
If x = 0 Then rng1.Interior.ColorIndex = 6
Next rng1
End Sub

HTH

DominicB