Audit Log

[Excel_Enthusiast]

Hi folks,

I'm sure i have done this before but can't remember how or find specifically what im looking for!

Essentially 2 tabs
1. Data
2. Audit

Whenever i change a date in a specific cell in tab 1, I want the Audit tab to create a new row recording the cell that changed and when it was changed.

Preferably VBA.

Thanks!

 

[offthelip]

You haven't stated what range of cells you want to check so I picked B2to C15 for testing : put this is in the worksheet code for the sheet you are checking for changes:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not (Intersect(Target, Range("B2:C15")) Is Nothing) Then
With Worksheets("Audit")
 LastRow = .Cells(Rows.Count, "A").End(xlUp).Row + 1
 .Cells(LastRow, 1) = Now()
 .Cells(LastRow, 2) = Target.Address
 End With
End If
End Sub