Compare data in two sheets and highlight differences in mismatches

Question

I have checked answers given in other questions, but since I am a newcomer to excel vba macros, I really can't find a way to get my problem solved using solutions provided.

EDIT: I am not able to write code on my own yet. I started one month ago with macros, copying and pasting from different posts, and editing as I needed, with many errors usually that I need to fix. It's pretty frustrating but I see this just too big of a problem to get it done on my own.

Thanks in advance for your kind help and shared knowledge.

I have two excel sheets that:

1. Contain different columns, of which I need to check three of them for matching values
2. Number of entries (rows) in both may differ (different lenght)
3. Values may or may not be sorted
4. For every value in row A, macro shall check value in row B and C, and compare against sheet2, and the same routine taking as reference values in sheet 2 against sheet 1.
5. Row A is numerical, B is numerical, and C is text

Example:

Sheet 1

    A         B            C        D        E  
1  Code     Revision     Status  
2  102        0            C  
3  102        1            A  
4   89        0            A  
5  216        0            C  
6  216        1            B  
7  216        2            A  
8  217        0            B  

Sheet 2

    A         B            C        D        E
1  Code     Revision     Status  
2  102        0            A  
3  102        1            A  
4   89        0            A  
5   90        0            A  
6  216        0            C  
7  216        1            B  
8  216        2            A  
9   16        0            A  

After the macro runs, the result in Sheet 1 and Sheet 2 should be that:

1. All values in column A in Sheet 2 that are missing in Sheet 1 shall be colored its background in, say, red.
2. All values in column A in Sheet 1 that are missing in Sheet 2 shall be colored its background in, say, red.
3. The same way, for values in column A that are matching in both sheets, missing 'Revisions' (Column B) in each sheet shall be highlighted the same way as in previous points 1 and 2
4. finally, for rows that match both 'Code' and 'Revision' (Column A and B) in both sheets, in case that the 'Status' (Column C) is not matching, highlight in Sheet 1.

The result would color red the following cells:

Sheet 1:

• C2
• A8

Sheet 2:

• A5
• A9

Thanks again.

Answer 1

Ok, I could manage to prepare this code that more or less does what I wanted, with some modifications to speed up the testing:

It checks sets of lists (3 columns) in two sheets, and verifies whether all conditions stated previously in this post match.

If matching occurs, it adds a new value in column E, with value 'Ok'.

This, combined to conditional formatting in the excel sheets, highlights rows that are found matching values en A, B and C.

The code seems to work and some improvements may be taken, but since I have to process some thousands of rows, I am afraid is gonna take too long.

Is there a more eficient way to do so, rather than looping through all the rows?

This is the code:

Sub Compare()

' Declare all the variables needed
  Dim rng1 As Range, rng2 As Range, rngName As Range, i As Integer, j As Integer

Dim varCode1 As Integer
Dim varRevision1 As Integer
Dim varStatus1 As String

Dim varCode2 As Integer
Dim varRevision2 As Integer
Dim varStatus2 As String

' Process second sheet
 For i = 2 To Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row
    Set rng1 = Sheets("Sheet2").Range("A" & i)
    varCode2 = Sheets("Sheet2").Range("A" & i).Value
    varRevision2 = Sheets("Sheet2").Range("B" & i).Value
    varStatus2 = Sheets("Sheet2").Range("C" & i).Value
    ' MsgBox "Code " & varCode2 & ", Rev. " & varRevision2 & ", Status " & varStatus2
    For j = 2 To Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
        Set rng2 = Sheets("Sheet1").Range("A" & j)
        If rng1.Value = rng2.Value Then
        varCode1 = rng2.Copy
        varRevision1 = Sheets("Sheet1").Range("B" & j).Value
        varStatus1 = Sheets("Sheet1").Range("C" & j).Value
        ' This will just write 'Ok' in cells with matches between column A and B

        If varRevision1 = varRevision2 And varStatus1 = varStatus2 Then
            Worksheets("Sheet2").Range("E" & i).Value = "Ok"
        End If

        End If

        Set rng2 = Nothing
    Next j
    Set rng1 = Nothing
Next i

' Process first sheet
For i = 2 To Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
    Set rng1 = Sheets("Sheet1").Range("A" & i)
    varCode1 = Sheets("Sheet1").Range("A" & i).Value
    varRevision1 = Sheets("Sheet1").Range("B" & i).Value
    varStatus1 = Sheets("Sheet1").Range("C" & i).Value
    ' MsgBox "Code " & varCode2 & ", Rev. " & varRevision2 & ", Status " & varStatus2
    For j = 2 To Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row
        Set rng2 = Sheets("Sheet2").Range("A" & j)
        If rng1.Value = rng2.Value Then
        varCode2 = rng2.Copy
        varRevision2 = Sheets("Sheet2").Range("B" & j).Value
        varStatus2 = Sheets("Sheet2").Range("C" & j).Value
        ' This will just write 'Ok' in cells with matches between column A and B

        If varRevision2 = varRevision1 And varStatus2 = varStatus1 Then
            Worksheets("Sheet1").Range("E" & i).Value = "Ok"
        End If

        End If

        Set rng2 = Nothing
    Next j
    Set rng1 = Nothing
Next i


End Sub