VBA - Compare two sheets and results paste on new sheet

Question

I have 2 sheets "2019 Project Detail" and "2019 Project Detail SOURCE", the structure is same, since "2019 Project Detail SOURCE" is a copy of "2019 Project Detail". Then I would like to check if there are any differences between these 2 sheets. If someone has changed any number / anything on sheet "2019 Project Detail". If so, then highlight them and also paste the reference of changed cell on 3rd sheet "Results" (i.e. "2019 Project Detail!AD4").

I have code that highlight the changes, but I can't figure out how to paste the changes on "Results" sheet.

Code:

Sub CompareAndHighlightDifferences()

Dim w1 As Worksheet, w2 As Worksheet, w3 As Worksheet

Set w1 = Sheets("2019 Project Detail")
Set w2 = Sheets("2019 Project Detail SOURCE")
Set w3 = Sheets("Results")

With w1
    For Each cel In .UsedRange
        If cel.Value <> w2.Cells(cel.Row, cel.Column).Value Then cel.Interior.Color = vbBlue

    Next cel
End With

End Sub

Could you advise me, please?

Many thanks!

What do you mean with *"Pate the reference"*? Write the address to ws3? — FunThomas, Mar 28 '19 at 13:02
Do you mean if cell AD4 is different you want "AD4" to appear on Results? And should it appear in cell AD4? — SJR, Mar 28 '19 at 13:03
In your `IF` condition, add: `w3.cells(, ).value = cel.value` — Zac, Mar 28 '19 at 13:06
@FunThomas - I mean put the address of changed cell to ws3 (i.e. Column A). — Srpic, Mar 28 '19 at 13:06
@SJR - Exactly, just on Results sheet it should appear in Column A (ideally from column A1 until Axxx). — Srpic, Mar 28 '19 at 13:07
Although, I would recommend to use arrays to go through your range rather than work with cells.. lot more efficient — Zac, Mar 28 '19 at 13:07

FunThomas · Accepted Answer · 2019-03-28T14:19:49.767

1

This piece of code will log all changes into w3:

Dim row As Long
row = 1

With w1
    For Each cel In .UsedRange
        If cel.Value <> w2.Cells(cel.row, cel.Column).Value Then
            cel.Interior.Color = vbBlue
            w3.Cells(row, 1) = cel.Address
            w3.Cells(row, 2) = cel.Value
            w3.Cells(row, 3) = w2.Cells(cel.row, cel.Column).Value
            row = row + 1
        End If
    Next cel
End With

edited Mar 28 '19 at 14:19

answered Mar 28 '19 at 13:08

FunThomas

23,043
3
18
34

Although this will work (I'm presuming that it would because I haven't tested it :)), but it's not recommended to work with cells. [Have a look at this](https://stackoverflow.com/questions/5387929/vba-macro-to-compare-all-cells-of-two-excel-files) – Zac Mar 28 '19 at 13:11
@Zac: I know, I know. just wanted to answer the question. If you start to optimize all the VBA code that is posted here, you will have a (unpaid) job for lifetime. BTW: If the sheet is not that big, the runtime is very little (for 150x100 cells, it was ready in far less than 1s, plus the code is straight forward and easy to understand. – FunThomas Mar 28 '19 at 13:19

score 1 · Answer 2 · answered Mar 28 '19 at 13:14

Probably, one of options will help to compare changes. Option 1 wil display values from both sheets in the same cell on "Results" worksheet. Option 2 can list names of different cells.

Sub CompareAndHighlightDifferences()
Dim w1 As Worksheet, w2 As Worksheet, w3 As Worksheet
Set w1 = Sheets("Sheet1")
Set w2 = Sheets("Sheet2")
Set w3 = Sheets("Sheet3")
For Each cel In w1.UsedRange
    If cel.Value <> w2.Cells(cel.Row, cel.Column).Value Then
        cel.Interior.Color = vbBlue
        'Option 1
        'w3.Cells(cel.Row, cel.Column).Value = w1.Name & " value: " & cel.Value & " / " & _
        'w2.Name & " value: " & w2.Cells(cel.Row, cel.Column).Value

        'Option 2
        lLastRow = Cells(Rows.Count, 1).End(xlUp).Row
        w3.Cells(lLastRow + 1, 1).Value = Split(cel.Address(True, False), "$")(0) & cel.Row
    End If
Next cel
End Sub

I would recommend not to use: `lLastRow = Cells(Rows.Count, 1).End(xlUp).Row` in your `FOR` loop. Get the last row outside the loop and just increment it once you are in the loop.. lots less resource hungry that way. Also, declare all your variables.. using `Option Explicit` will help — Zac, Mar 28 '19 at 14:11

VBA - Compare two sheets and results paste on new sheet

2 Answers2