Compare two columns in different sheets, highlight unique values.

[Scoober]

Hi All,

I would like a to have a VBA script that compares two columns that are in different sheets and highlights the unique values in sheet 1. As below:
Sheet 1 Sheet2
Column A Column A
Apple Apple
Orange Pineapple
Pineapple Orange
Pear Coconut
Tomato

Transformed With VBA to:

Sheet 1 Sheet2
Column A Column A
Apple Apple
Orange Pineapple
Pineapple Orange
Pear Coconut
Tomato

So it is Sheet 1's column that is compared to Sheet2 no highlighting needed in sheet2 there can be unique values in that one.

Thank you for your help!

 

[Scoober]

my apologies the spaces went away in my example, so its a bit unclear. The "tomato" value is in sheet 2 column so not highlighted

 

[bebo021999]

MAby something like this:

Option Explicit
Sub highlight()
Dim lr&, r1 As Range, r2 As Range, cell As Range
With Sheets("Sheet2")
    lr = .Cells(Rows.Count, "A").End(xlUp).Row
    Set r2 = .Range("A1:A" & lr)
End With
With Sheets("Sheet1")
    lr = .Cells(Rows.Count, "A").End(xlUp).Row
    Set r1 = .Range("A1:A" & lr)
    For Each cell In r1
        If r2.Find(cell.Value) Is Nothing Then cell.Font.Color = vbRed
    Next
End With
End Sub

 

[Scoober]

Works! Thank you so much!