[SOLVED] Compare two cells belonging to two different columns

Compare two cells belonging to two different columns

I want to do it in VBA, compare two cells belonging to two columns in Excel and highlight the cell in difference.

I had tried something like this but it's not working.

Sub find_matches()
Dim CompareRange As Range, x As Range

Set CompareRange = Range("C1:C5")
Set x = Range("A1:A5")

For Each x In CompareRange
    If x <> CompareRange Then
        x.Interior.Color = RGB(255, 87, 87)
    End If
Next x
End Sub

Solution

Make a minor change in your IF statement. Thats it.

Private Sub find_matches()
Dim CompareRange As Range, x As Range
Set ts = Worksheets("Matching") 'The sheet name
Set CompareRange = ts.Range("C1:C5")
Set x = ts.Range("A1:A5")
For Each cc In CompareRange
    If cc.Value <> ts.Cells(cc.Row, 1).Value Then
        cc.Interior.Color = RGB(255, 87, 87)
    End If
Next cc
End Sub