worksheet audit trail.vb

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