Excel Track changes in Excel vba code

[ASJ]

Hello everyone,

I have a vba code which works fine while updating cell value in excel, except while deleting or adding row.
Could you please help (I am new to vba macros) with the code, where if the row is deleted or added then do nothing or else run through the remaining code.

Below is the code.


Private Sub Worksheet_Change(ByVal Target As Range)\For Each j In Target
Dim wb As Workbook
Dim ws2 As Worksheet
Dim NextRow As Long
Set wb = ThisWorkbook
Set ws2 = wb.Worksheets("Change Log") 'Sheet name where i am tracking the changes
NextRow = ws2.Cells(Rows.Count, 1).End(xlUp).Row + 1
Dim CellAdd As String
CellAdd = j.Address
Dim sht As String
sht = ActiveSheet.Name
Dim newValue As String
newValue = j.Value
Application.EnableEvents = False
Application.Undo
oldValue = j.Value
Application.Undo
Application.EnableEvents = True
ws2.Cells(NextRow, 1) = sht & " " & "-" & " " & CellAdd & " was changed to '" & newValue & "' from '" & oldValue & "' by " & Environ("username") & " " & "on" & " " & Format(Now(), "M-DD-YYYY H:MM:ss")
Next j
End Sub

[georgiboy]

Try it as below:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim NextRow As Long
Dim newValue As String
Dim ws2 As Worksheet
Dim j As Range

If Target.Address <> Target.EntireRow.Address And Target.Address <> Target.EntireColumn.Address Then
Application.ScreenUpdating = False
Set ws2 = Worksheets("Change Log") 'Sheet name where i am tracking the changes
For Each j In Target
NextRow = ws2.Cells(Rows.Count, 1).End(xlUp).Row + 1
newValue = j.Value
Application.EnableEvents = False
Application.Undo
oldValue = j.Value
Application.Undo
Application.EnableEvents = True
ws2.Cells(NextRow, 1) = ActiveSheet.Name & " " & "-" & " " & j.Address & " was changed to '" & newValue & "' from '" & oldValue & "' by " & Environ("username") & " " & "on" & " " & Format(Now(), "M-DD-YYYY H:MM:ss")
Next j
Application.ScreenUpdating = True
End If
End Sub

[ASJ]

It's working! Thank you so much Georgiboy. Very much appreciated for your efforts you have put in for looking into this.

[georgiboy]

You're welcome ASJ, thanks for the feedback.