Ive watched the following video to create an audit trail
The code Im using is
but I am getting a yellow hightlight at
RowInsertion = wsTrail.Cells(Rows.Count, "A").End(xlUp).Row + 1
any ideas on a fix to this?
The code Im using is
Option Explicit | |
Dim wsLOE As Worksheet, wsTrail As Worksheet | |
Dim PreviousValue As Variant | |
Private Sub Worksheet_Change(ByVal Target As Range) | |
Set wsTrail = ThisWorkbook.Worksheets("Trail") | |
If Target.Column = 3 Or Target.Column = 4 Then | |
If Target.Value <> PreviousValue Then | |
Call Log_Change(Target) | |
End If | |
End If | |
End Sub | |
Private Sub Log_Change(ByVal Cell_Target As Range) | |
Dim RowInsertion As Long | |
RowInsertion = wsTrail.Cells(Rows.Count, "A").End(xlUp).Row + 1 | |
wsTrail.Cells(RowInsertion, "A").Value = _ | |
Application.UserName & " changed cell " & Cell_Target.Address _ | |
& " from " & PreviousValue & " to " & Cell_Target.Value _ | |
& " on LOE worksheet" | |
End Sub | |
Private Sub Worksheet_SelectionChange(ByVal Target As Range) | |
PreviousValue = Target.Value | |
End Sub | |
Private Sub Worksheet_Deactivate() | |
Set wsLOE = Nothing | |
Set wsTrail = Nothing | |
End Sub |
but I am getting a yellow hightlight at
RowInsertion = wsTrail.Cells(Rows.Count, "A").End(xlUp).Row + 1
any ideas on a fix to this?