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
15
Office Version
365, 2016
Platform
Windows
Ah good callout! I thought they were but realized there was a value out to the right! Thank you so so much!
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
41,201
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback.
 

Walkerwood9

New Member
Joined
Jun 23, 2020
Messages
15
Office Version
365, 2016
Platform
Windows
You're welcome & thanks for the feedback.
Okay don't hate me...I'm back! What would the code need to be if they were different sizes? I tried implementing it into the more complex workbook that I have and I think there are more rows so it is throwing an error on the first For Loop??
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
41,201
Office Version
365
Platform
Windows
What error do you get?
 

Walkerwood9

New Member
Joined
Jun 23, 2020
Messages
15
Office Version
365, 2016
Platform
Windows
Type Mismatch Error. The two sheets are rather large and there are updates every week. I originally was just using a simple data set to try and figure out how to do it but now I am running into new problems haha.

Basically there could be new rows or columns each week I get an updated sheet and I want to be able to see what changed without manually having to go through all 3,000 lines. It would be awesome to have the cells highlighted that have changed or been added.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
41,201
Office Version
365
Platform
Windows
Type mismatch suggests that some of the cells contain a formula error such as #N/A, #VALUE, #REF etc.
 

Walkerwood9

New Member
Joined
Jun 23, 2020
Messages
15
Office Version
365, 2016
Platform
Windows
Ah oh yes, There are for sure some cells with those in them. Is there a way around that?
 

Walkerwood9

New Member
Joined
Jun 23, 2020
Messages
15
Office Version
365, 2016
Platform
Windows
I took out all the columns with formulas and it still gave me that error.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
41,201
Office Version
365
Platform
Windows
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("pcode")
   Ary1 = ws.Range("A1").CurrentRegion.Value2
   Ary2 = Sheets("sheet1").Range("A1").CurrentRegion.Value2
   
   With CreateObject("Scripting.dictionary")
      For r = 1 To UBound(Ary2)
         If Not IsError(Ary2(r, 1)) Then .Item(Ary2(r, 1)) = r
      Next r
      For r = 1 To UBound(Ary1)
         If Not IsError(Ary1(r, 1)) Then
            If .Exists(Ary1(r, 1)) Then
               For c = 1 To UBound(Ary1, 2)
                  If Not IsError(Ary1(r, c)) And Not IsError(Ary2(.Item(Ary1(r, 1)), c)) Then
                     If Ary1(r, c) <> Ary2(.Item(Ary1(r, 1)), c) Then
                        ws.Cells(r, c).Interior.Color = vbRed
                     End If
                  End If
               Next c
            Else
               ws.Rows(r).Interior.Color = vbRed
            End If
         End If
      Next r
   End With
End Sub
This should ignore any cells with errors
 

Watch MrExcel Video

Forum statistics

Threads
1,100,041
Messages
5,472,136
Members
406,805
Latest member
AlesD6

This Week's Hot Topics

Top