Highlight Differences between Two Sheets, Accounting for Added Rows

[michaeltsmith93]

Hi all,

I have found a million solutions to highlight differences, but I'm having trouble finding something that accommodates the addition of a row in the middle of my data.

The idea is that I have two sheets with a few dozen columns--let's call these sheets A and B, with B being the newer version of the data. Conditional formatting is an easy solution, but it will Fill all cells below a newly added row in sheet B if I use a formula like =A1<>Sheet2!A1. If an entirely new row is added, I would like all cells in this row to be highlighted, and then I would like for it to resume the comparison, recognizing that it now needs to compare the following row to Rows(newly added row) -1, so to speak. Could someone please point me in the right direction here?

Sub Rectangle1_Click()


    Dim wsA As Worksheet
    Dim wsB As Worksheet
    Dim varA As Variant
    Dim varB As Variant
    Dim LColumn As Long
    Dim RangeToCheck As Range
    Dim iRow As Long
    Dim iCol As Long
    
    Set wsA = Worksheets(2)
    Set wsB = Worksheets(1)
    
    LColumn = wsB.Cells.Find(What:="*")
    
    RangeToCheck = Range(Cells(5, 1), Cells(1000, LColumn))
    
    varA = wsA.Range(RangeToCheck)
    varB = wsB.Range(RangeToCheck)
    
    For iRow = LBound(varA, 1) To UBound(varA, 1)
        For iCol = LBound(varA, 2) To UBound(varA, 2)
            If Not varA(iRow, iCol) = varB(iRow, iCol) Then
            
            varB(iRow, iCol).Interior.Color = 3
            
[COLOR=#ff0000]            'code to account for added row[/COLOR]
            
            End If
        Next iCol
    Next iRow


End Sub

 

[Fluff]

This will create a new sheet with the names that have been removed

Sub michaeltsmith93()
   Dim Cl As Range
   Dim Ws1 As Worksheet, Ws2 As Worksheet
   
   Set Ws1 = Sheets("[COLOR=#ff0000]Pcodes[/COLOR]")
   Set Ws2 = Sheets("[COLOR=#ff0000]Postcodes[/COLOR]")
   With CreateObject("scripting.dictionary")
      .CompareMode = 1
      For Each Cl In Ws2.Range("A2", Ws2.Range("A" & Rows.Count).End(xlUp))
         .Item(Cl.Value) = Empty
      Next Cl
      For Each Cl In Ws1.Range("A2", Ws1.Range("A" & Rows.Count).End(xlUp))
         If .Exists(Cl.Value) Then .Remove Cl.Value
      Next Cl
      Sheets.Add(, Sheets(Sheets.Count)).Name = "Removed"
      Sheets("removed").Range("A2").Resize(.Count).Value = Application.Transpose(.Keys)
   End With
End Sub

Change values in red to suit