VBA - sheets comparison

[VictorKZ]

Hello everyone.

I need a VBA to compare two sheets and highlights the unique values.

I have this VBA, but it also highlights if a cell changed it´s position, which I don’t need. I only need the VBA to highlight which cells have different values and the unique cells in each sheets.

Sub Compare_Two_Excel_Sheets()
    'Define Fields
    Dim iR As Double, iC As Double, oRw As Double
    Dim iRow_M As Double, iCol_M As Double
    Dim s1 As Worksheet, s2 As Worksheet
    Dim s3 As Worksheet
    Set s1 = ThisWorkbook.Sheets(1)
    Set s2 = ThisWorkbook.Sheets(2)
    Set s3 = ThisWorkbook.Sheets(3)
    iRow_M = s1.UsedRange.Rows.Count
    iCol_M = s1.UsedRange.Columns.Count
    For iR = 1 To iRow_M
    For iC = 1 To iCol_M
        s1.Cells(iR, iC).Interior.Color = xlNone
        s2.Cells(iR, iC).Interior.Color = xlNone
        If s1.Cells(iR, iC) <> s2.Cells(iR, iC) Then
           s1.Cells(iR, iC).Interior.Color = vbYellow
           s2.Cells(iR, iC).Interior.Color = vbYellow
           oRw = oRw + 1
           s3.Cells(oRw, 1) = s1.Cells(iR, iC)
           s3.Cells(oRw, 2) = s2.Cells(iR, iC)
        End If
    Next iC
    Next iR
End Sub

Thanks for listening.

 

[Leo Skywalker]

Cells don't change their position.

If Cell B2 contains "Any Text" in Sheet1, and nothing in Sheet2, then B2 has two different values in each Sheet, it has to be highlighted.
If Cell C2 contains nothing in Sheet1, and "Any text" in Sheet2, then C2 has two different values in each Sheet, it has to be highlighted.

Cells B2 and C2 have not changed their position in the other Sheet, each one of them has changed its value independently.

If A5 contains 10 and F16 contains 20 in Sheet1, and then A5 contains 20 and F16 contains 10 in Sheet2, it doesn't mean they have changed places.

 

[VictorKZ]

Thanks, i have an exemple:
As we can see in this imagen, it highlighted the lines 423 in both sheets, even though they are the same in values.
How can i change the VBA to not highlight cells with the same value on different lines. I need to highlight only new and unique values.
Thank you for your help.