audit log VBA code not capturing IF formula result

I have an audit log VBA code, to capture changes from several worksheets into one worksheet called "LRLog".

This only writes value changes. Formatting changes are not required to be captured.

The VBA code works fine, but does NOT capture changes in those cells which are determined by IF formula based on data input in other cells.

Users will make changes in columns C, D, E, F, and H, with IF formula in columns A and G showing a result, dependent on the data input in columns D, E, and F.
However, currently, any changes to cols A or G (because of the outcome of formulae in col A or G) are not captured in the audit log, as attached:
(yellow blocks in the attached Audit log are what is expected should be displayed).

The following VBA has been used in the sheets where changes will take place.
------------------------------------------

Dim PreviousValue As Variant
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Errb As Integer
    On Error GoTo ErrTrap:
    If Target.Value <> PreviousValue Then
      With Sheets("LRlog").Cells(65000, 1).End(xlUp)
        .Offset(1, 0).Value = Application.UserName
        .Offset(1, 1).Value = ActiveSheet.Name
        .Offset(1, 2).Value = "changed cell"
        .Offset(1, 3).Value = Target.Address
        .Offset(1, 4).Value = "from"

        .Offset(1, 5).Value = PreviousValue
        .Offset(1, 6).Value = "to"
        .Offset(1, 7).Value = Target.Value
        .Offset(1, 8).Value = "on"
        .Offset(1, 9).Value = Format(Date, "d/mmm/yyyy")
        .Offset(1, 10).Value = Format(Now(), "hh:mm:ss")
        
               
      End With
    End If
    Exit Sub
ErrTrap:
  ErrNum = Err
  
  If ErrNum = 13 Then
    '*** Multiple cells have been selected, treat them as one merged group*****
    Resume Next
  End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    PreviousValue = Target.Value
End Sub

------------------------------------

Can anyone help with the VBA code so that any changes to cols A or G (because of the outcome of formulae in col A or G) are also captured in the audit log?

I would also like to include good error handling in this project if possible.

Any chance you could upload a sample workbook?

Click on GO ADVANCED, scroll down and click Manage Attachments.

PS When posting code can you please use CODE tags?

The Change event of a worksheet is not triggered when a value changes due to the result of a formula.

What formula(s) do you have in column A & G?

Hi, I found this tracking log on Ozgrid's site (is mentioned in the code), works for me, you will probably have to tailor it to your needs but is does also mention formula resuls.

Option Explicit

'*  Link        : http://www.ozgrid.com/VBA/track-changes.htm
'*  Modified    : 13-03-2018 by Hans Hallebeek (HC&TS)

'*  Logs changes made in the entire workbook in a hidden worskheet named 'changeLogs'
'*  Logs WORKSHEET,CELL CHANGED,OLD VALUE,NEW VALUE,TIME OF CHANGE,DATE OF CHANGE,USERNAME
'*  To realy hide the worsksheet change .Visible = xlSheetHidden to .Visible = xlSheetVeryHidden
'*  Password is coded in the VBa: 'Secret'

Dim vOldVal                 'Must be at top of module
Dim logsWs  As Worksheet    'Must be at top of module

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim bBold As Boolean
On Error GoTo makeLogSheet
Set logsWs = Worksheets("changeLogs")
Err.Clear
If Target.Cells.Count > 1 Then Exit Sub
On Error Resume Next
    With Application
         .ScreenUpdating = False
         .EnableEvents = False
    End With
    If IsEmpty(vOldVal) Then vOldVal = "Empty Cell"
    bBold = Target.HasFormula
        With logsWs
            .Unprotect Password:="Secret"
            If .Range("A1") = vbNullString Then
               .Range("A1:G1") = Array("WORKSHEET", "CELL CHANGED", "OLD VALUE", _
                   "NEW VALUE", "TIME OF CHANGE", "DATE OF CHANGE", "USERNAME")
            End If
            With .Cells(.Rows.Count, 1).End(xlUp)(2, 1)
                  .Value = ActiveSheet.Name
                  .Offset(0, 1) = Target.Address
                  .Offset(0, 2) = vOldVal
                      With .Offset(0, 3)
                        If bBold = True Then
                          .ClearComments
                          .AddComment.Text Text:= _
                               "OzGrid.com:" & Chr(10) & "" & Chr(10) & _
                                  "Bold values are the results of formulas"
                        End If
                          .Value = Target
                          .Font.Bold = bBold
                      End With
                .Offset(0, 4) = Time
                .Offset(0, 5) = Date
                .Offset(0, 6) = Environ$("Username")
            End With
            .Cells.Columns.AutoFit
            .Protect Password:="Secret", UserInterFaceOnly:=True
            .Visible = xlSheetHidden
        End With
    vOldVal = vbNullString
    With Application
         .ScreenUpdating = True
         .EnableEvents = True
    End With
On Error GoTo 0
Exit Sub
makeLogSheet:
ThisWorkbook.Worksheets.Add
ActiveSheet.Name = "changeLogs"
ActiveSheet.Visible = xlSheetHidden
Resume
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Count = 1 Then vOldVal = Target
End Sub

I suggest you visit that site for further information.