Excel VBA Code to Compare Rows in Two Different Sheets and Highlight Only the Cells that are different/unique

Walkerwood9

New Member
Joined
Jun 23, 2020
Messages
16
Office Version
365, 2016
Platform
Windows
I have a workbook that has two sheets in it and I want to compare these sheets (Test1 and Test 2) and highlight only the individual cells that are different/unique. The code I will provide below highlights the row if their is a match but I need the code to highlight only the cell that is NOT a match. I pulled this code from another Forum for help/reference.


VBA Code:
Sub Test_Sheet()

Dim sheetOne As Worksheet
Dim sheetTwo As Worksheet
Dim lastRow As Long
Dim thisRow As Long
Dim lastCol As Long
Dim thisCol As Long
Dim foundRow As Range
Dim lastFoundRow As Long
Dim searchRange As Range
Dim isMatch As Boolean

' Set up the sheets
Set sheetOne = Sheets("Test1")
Set sheetTwo = Sheets("Test 2")

' Find the last row of the active sheet
lastRow = sheetOne.Cells(sheetOne.Rows.Count, "A").End(xlUp).Row

' Set the search range on the other sheet
Set searchRange = sheetTwo.Range("A1:A" & sheetTwo.Cells(sheetTwo.Rows.Count, "A").End(xlUp).Row)

' Look at all rows
For thisRow = 1 To lastRow
    ' Find the last column on this row
    lastCol = sheetOne.Cells(thisRow, sheetOne.Columns.Count).End(xlToLeft).Column
   
    ' Find the first match
    Set foundRow = searchRange.Find(sheetOne.Cells(thisRow, "A").Value, searchRange(searchRange.Count), xlValues, xlWhole)
   
    ' Must find something to continue
    Do While Not foundRow Is Nothing
        ' Remember the row we found it on
        lastFoundRow = foundRow.Row
       
        ' Check the found row has the same number of columns
        If sheetTwo.Cells(lastFoundRow, sheetTwo.Columns.Count).End(xlToLeft).Column = lastCol Then
            ' Assume it's a match
            isMatch = True
           
            ' Look at all the column values
            For thisCol = 1 To lastCol
                ' Compare the column values
                If sheetTwo.Cells(lastFoundRow, thisCol).Value <> sheetOne.Cells(thisRow, thisCol).Value Then
                    ' No match
                    isMatch = False
                    Exit For
                End If
            Next thisCol
           
            ' If it's still a match then highlight the row
            If isMatch Then sheetOne.Range(sheetOne.Cells(thisRow, "A"), sheetOne.Cells(thisRow, lastCol)).Interior.ColorIndex = 3
        End If
       
        ' Find the next match
        Set foundRow = searchRange.Find(sheetOne.Cells(thisRow, "A").Value, foundRow, xlValues, xlWhole)
       
        ' Quit out when we wrap around
        If foundRow.Row <= lastFoundRow Then Exit Do
    Loop
Next thisRow

End Sub
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

braindiesel

Well-known Member
Joined
Mar 16, 2009
Messages
552
Office Version
365, 2019, 2010
Platform
Windows
Does the entire row need to match or just a targeted cell...
This is just the targeted cell using conditional formatting... no code required
Replace my named ranges with the ranges on the opposite sheets
Book1
ABC
1
2ApplesApples
3BananasBananas
4CherriesDates
5DatesEggplant
6EggplantGrapes
7FigsHoneydew
8Grapes
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C2:C7Expression=COUNTIF(Test1,C2)=0textNO
A2:A8Expression=COUNTIF(Test2,A2)=0textNO
 

Walkerwood9

New Member
Joined
Jun 23, 2020
Messages
16
Office Version
365, 2016
Platform
Windows
Does the entire row need to match or just a targeted cell...
This is just the targeted cell using conditional formatting... no code required
Replace my named ranges with the ranges on the opposite sheets
Book1
ABC
1
2ApplesApples
3BananasBananas
4CherriesDates
5DatesEggplant
6EggplantGrapes
7FigsHoneydew
8Grapes
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C2:C7Expression=COUNTIF(Test1,C2)=0textNO
A2:A8Expression=COUNTIF(Test2,A2)=0textNO
Just a targeted cell, and I have to put this in a macro.
 

braindiesel

Well-known Member
Joined
Mar 16, 2009
Messages
552
Office Version
365, 2019, 2010
Platform
Windows
OK, so did I solve it without a macro for you?
 

Walkerwood9

New Member
Joined
Jun 23, 2020
Messages
16
Office Version
365, 2016
Platform
Windows
OK, so did I solve it without a macro for you?
I appreciate the response, but unfortunately not. It needs to be VBA code I can put in a macro and run on the entire sheet (38 columns)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,592
Office Version
365
Platform
Windows
Are the values in Col A unique, or can they occur multiple times?
 

Walkerwood9

New Member
Joined
Jun 23, 2020
Messages
16
Office Version
365, 2016
Platform
Windows
The Values in column A are unique yes. They cannot occur multiple times only once
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,592
Office Version
365
Platform
Windows
Ok, how about
VBA Code:
Sub Walkerwood()
   Dim Ary1 As Variant, Ary2 As Variant
   Dim r As Long, c As Long
   Dim Ws As Worksheet
   
   Set Ws = Sheets("Test1")
   Ary1 = Ws.Range("A1").CurrentRegion.Value2
   Ary2 = Sheets("Test2").Range("A1").CurrentRegion.Value2
   
   With CreateObject("Scripting.dictionary")
      For r = 1 To UBound(Ary2)
         .Item(Ary2(r, 1)) = r
      Next r
      For r = 1 To UBound(Ary1)
         If .Exists(Ary1(r, 1)) Then
            For c = 1 To UBound(Ary1, 2)
               If Ary1(r, c) <> Ary2(.Item(Ary1(r, 1)), c) Then
                  Ws.Cells(r, c).Interior.Color = vbRed
               End If
            Next c
         Else
            Ws.Rows(r).Interior.Color = vbRed
         End If
      Next r
   End With
End Sub
 

Walkerwood9

New Member
Joined
Jun 23, 2020
Messages
16
Office Version
365, 2016
Platform
Windows
Getting a subscript out of range error on this line:
If Ary1(r, c) <> Ary2(.Item(Ary1(r, 1)), c) Then
 

Watch MrExcel Video

Forum statistics

Threads
1,102,241
Messages
5,485,597
Members
407,504
Latest member
inexperiencedOne

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top