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]

You could CF with this formula
=MATCH(A1,Sheet2!$A$1:$A$1000,0)

 

[michaeltsmith93]

Thanks for this, Fluff. This doesn't quite accomplish what I want, I don't think. I would like for it to compare all cells within the two arrays of data. I see what you're getting at with absolute references in Match, but how can I modify it such that all cells are being compared?

Alternatively, in my code above, how could I go about including that Row()-1 idea?

 

[Fluff]

All cells are being compared

Select A1 downwards on "sheet1" & use that formula. That way if any cell in Col A sheet1 exists in col A sheet2 it will be highlighted.

 

[michaeltsmith93]

Yes, but I have data in columns all the way out to Column BK. How would I go about ensuring that that data is also compared? I can't add 60-something rules because I will need to do this every week or so. Thoughts?

 

[Fluff]

In that case can you please explain exactly what you are trying to do?

 

[michaeltsmith93]

Sure, and I appreciate you sticking with me.

I have data approximately spanning the range A1:BK250. This data is comprised of a list of people with attributes associated with each person in the columns of their row. The data is sorted alphabetically by last name. As a result, when we add a new person to this list, they're not added to the end, but somewhere in the middle. Additionally, changes are made on an ongoing basis to individual cells as certain attributes change (i.e., correction of email address, change of a date, etc.).

On an ad hoc basis, but generally every week, I need to highlight all changes between the current version and the previously extracted version. These highlights are meant to include all individual changes, and when a new person is added to the list, the entire row should be highlighted. The track changes function has been rejected since it makes it look like a mess.

This sheet is a part of a larger workbook, so what I'm doing is first copying the sheet over to a Changes Summary workbook that contains only these intermittently extracted versions. The purpose of the code above is to compare the new version to the previous version, as the most recent version will always be added Before all sheets in this Changes Summary workbook.

 

[Fluff]

In that case how about
=A1<>IFERROR(INDEX(Sheet2!A$1:A$9000,MATCH($A1,Sheet2!$A$1:$A$9000,0)),"")

 

[michaeltsmith93]

Fluff, thank you so much for this. It looks like it's accomplishing exactly what it should, although it made me realize that I forgot to mention something--sometimes, people are deleted from the list. This complicates things because obviously, I'm getting highlights on Sheet B of cells that are after rows that have been removed because the person on that row has been removed from the list. This was the reason that I realized that I needed to go a VBA route--the other thing to add to the code above is for it to create a new worksheet and summarize any rows that are on Sheet A (previous version) but not on Sheet B (current version), thereby listing all people that were removed from the list. The highlighting component of the For Next would then need to adjust for these deletions as well.

This might be beyond what's reasonable to ask of you or anyone on the forum. Thoughts?

 

[Fluff]

If you don't want to highlight new people, then use
=A1<>INDEX(Postcodes!A$1:A$9000,MATCH($A1,Postcodes!$A$1:$A$9000,0))