Need help with the below vb script. The script works however I need it to also "track" the changes on fields that contain formulas. I have a 'Timeline' sheet that has a column titled 'Task End Date' which has formula (=IF(E8="Enter Weeks","",IFERROR((D8+(E8*7)),"")) in it. The 'Change Log' script does not log the changes on this field because it is a calculated field. I require the 'Change Log' to capture the changes. The trigger to start capturing the changes on the 'Change Log' should be IF cell M3="YES"on sheet A3. If the cell M3=NO ,I do not require any changes to be logged.
Code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If ActiveSheet.Name = "Change Log" Then Exit Sub
Application.EnableEvents = False
NewVal = Target.Value
Application.Undo
oldVal = Target.Value
UserName = Environ("Username")
lr = Sheets("Change Log").Range("A" & Rows.Count).End(xlUp).Row + 1
Sheets("Change Log").Range("A" & lr) = Now
Sheets("Change Log").Range("B" & lr) = ActiveSheet.Name
Sheets("Change Log").Range("C" & lr) = Target.Address
Sheets("Change Log").Range("D" & lr) = oldVal
Sheets("Change Log").Range("E" & lr) = NewVal
Sheets("Change Log").Range("F" & lr) = UserName
Target = NewVal
Application.EnableEvents = True
End Sub
