Adding rows to a worksheet and checking for changes in all cells throughout the worksheet after the rows were added

If you add this sub, the code will stop at line 5 if a cell is changed.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then
    Exit Sub
End If
Stop
End Sub

Select allOpen in new window

Long time since coding in VB. So the code is looking for changes in the worksheet .. Private Sub Worksheet_Change(ByVal Target As Range)
Did I need to specify the range in the code above?
I see that it continues to go through the rows but how does it stop at line 5 if change? Couldnt I just compare the 2 worksheets and highlight differences via vb code or maybe a function in excel that compares and highlights differences? Thanks again for your help really appreciate. I am trying to set up a test.

Did I need to specify the range in the code above?

No, but it was designed to show just the first change. This is probably what you are looking for. Before you add the row, run the CopySheet macro or if you are using a macro to add the row, call CopySheet from that macro. It creates a copy of the current sheet and names it "Before". If "Before" already exists it is deleted first.

Sub CopySheet()
    On Error Resume Next
    Application.DisplayAlerts = False
    Sheets("Before").Delete
    On Error GoTo 0
    Application.DisplayAlerts = True
    
    ActiveSheet.Copy Before:=ActiveSheet
    ActiveSheet.Name = "Before"
End Sub

Select allOpen in new window

After the row is added, run this macro which highlights all the changed cells yellow.

Sub Compare()
Dim wsBefore As Worksheet
Dim cel As Range

Set wsBefore = Sheets("Before")

For Each cel In ActiveSheet.UsedRange.Cells
    If cel <> wsBefore.Range(cel.Address) Then
        cel.Style = "Note"
    End If
Next
End Sub

Select allOpen in new window

This compares the entire before spreadsheet to the entire after spreadsheet? By the way they are the same identical copies only the one has my updates. The spreadsheet is huge and there are alot of formulas so I wanted to make sure when adding various rows that the only changes are the new rows I added. Test to make sure other cells were not affected by my adding the new rows. Then I can see if changes occured prior to making actual changes in production. Compare the sheets and make sure aside from my additions that nothing else was affected in the spreadsheet. Thanks again also what does cel.Styly  = "Note" mean? Really appreciate your help. I am used to working with databases not so much spreadsheets so I want to make sure I test.

This will clear the yellow formatting when you are done.

Sub ClearHighlights()
Dim cel As Range

For Each cel In ActiveSheet.UsedRange.Cells
    cel.Style = "Normal"
Next
End Sub

Select allOpen in new window

You also implied that you are a new Excel user, so you may not be aware of the following easy way to call any macro. Let's assume you will be running my 'Compare' macro a lot. If so then press Alt+F8 to open the "Macro" dialog window. Select the 'Compare' macro, click ‘Options…’,  hold down the Shift key and type the letter 'C'  (or any other letter) and click ‘OK’. Then anytime you want to run the macro press Ctrl+Shift+C.

Thanks very helpful. I have to brush up on my vb and appreciate the extra tips.

You’re welcome and I’m glad I was able to help.

If you expand the “Full Biography" section of my profile you’ll find links to some articles I’ve written that may interest you including a new one concerning your Experts Exchange rank.

Marty - Microsoft MVP 2009 to 2017
              Experts Exchange Most Valuable Expert (MVE) 2015, 2017
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2018
              Experts Exchange Top Expert VBA 2018
              Experts Exchange Distinguished Expert in Excel 2018