Efficient way of comparing 2 tabs in excel

[BDexcel]

Hi all,

Just wondered is there any VBA I can us to compare data and highlight not only new data but if any of the old data changes?

I have a report I get each day and I simply want an efficient way of comparing it to the previous days data. Which will allow me 1. to see if any new lines have been added (each line has an ID so I could use this) and 2. if any of the old existing data cells information have changed also.

Appreciate any guidance you could give.

 

[ManiacB]

You can do a straight cell by cell loop check, and write the difference to a new sheet, something like this:

Sub comparetwo()
Dim i, u As Long
Dim wc, wp, wr As Worksheet
Dim lr As Long

Application.ScreenUpdating = False
Set wc = Sheets("Current")
Set wp = Sheets("PY")
Set wr = Sheets("Result")
u = 2
lr = Cells(Rows.Count, 1).End(xlUp).Row
wr.UsedRange.ClearContents
wr.Range("A1:H1").Value = wc.Range("A1:H1").Value
For i = 2 To lr
    If wc.Cells(i, 3).Value = wc.Cells(i, 3).Value And wc.Cells(i, 8).Value <> "Blank" Then
        Range(wc.Cells(i, 1), wc.Cells(i, 8)).Copy
        wr.Cells(u, 1).PasteSpecial xlWhole
        wr.Cells(u, 7).Value = wp.Cells(i, 7).Value - wc.Cells(i, 7).Value
        u = u + 1
    End If
    
Next i

With Application
    .CutCopyMode = False
    .ScreenUpdating = True
End With
End Sub

 

[BDexcel]

Thanks ManiacB, tried this but it just copied all over to a new tab...

Is there any VBA that could take each line ID and compare all following cells on the other tab and highlight any differences?

 

[Joe4]

Just wondered is there any VBA I can us to compare data and highlight not only new data but if any of the old data changes?

I have a report I get each day and I simply want an efficient way of comparing it to the previous days data. Which will allow me 1. to see if any new lines have been added (each line has an ID so I could use this) and 2. if any of the old existing data cells information have changed also.

Appreciate any guidance you could give.

Quite frankly, I find this task much easier to do with Microsoft Access, using the Unmatched Query Wizard, which will walk you through a lot of the data setup.
I don't know if you have access to Microsoft Access, but if you do, this may be a simpler option.

 

[CsJHUN]

Is it possible to use program called "Spreadsheet Compare 2016" its free tool for Excel