VBA to track changes to excel sheet

[JM123]

I am trying to get excel to track changes made to a large excel sheet that we use to store all the information relating to work orders. It is accessed by multiple people on our contract.

I want the changes to be tracked in a seperate sheet to the main worksheet and show the old value, new value, name of person who made the change and when the change was made.

I found the below code on a thread from 2010 that seems to get me close to what i am looking for however column 23 of our sheet contains formula that adds 8% to the value in column 22 as this is our management fee and this is causing the code to output change records for all 3000 rows of this column every time the workbook is opened. An example of the change log being created is Cell(2480,23) changed from '3486.26' to '3486.2579'.

I believe it is creating this changelog because the sheet only displays value to 2 d.p as it is formated to currency but the fomula is outputting the whole value.

Thread is VBA code to track changes in workbook

Option Explicit
 
Const LiveWS As String = "Sheet1"
Const AuditWS As String = "Audit"
 
Private Sub Workbook_Open()
 
  Dim iRow As Integer
  Dim iCol As Integer
  Dim iLastRow As Long
 
  For iRow = 1 To 3000
    For iCol = 1 To 40
      If Sheets(AuditWS).Cells(iRow, iCol).Value <> Sheets(LiveWS).Cells(iRow, iCol).Value Then
        iLastRow = Sheets(AuditWS).Cells(Rows.Count, 1).End(xlUp).Row
        Sheets(AuditWS).Cells(iLastRow + 1, 1) = "Cell(" & CStr(iRow) & "," & CStr(iCol) & ") " _
          & "changed from '" & Sheets(AuditWS).Cells(iRow, iCol).Value & "' " _
          & "to '" & Sheets(LiveWS).Cells(iRow, iCol).Value & "'"
        Sheets(AuditWS).Cells(iRow, iCol) = Sheets(LiveWS).Cells(iRow, iCol).Value
      End If
    Next iCol
  Next iRow
 
  iLastRow = Sheets(AuditWS).Cells(Rows.Count, 1).End(xlUp).Row
  Sheets(AuditWS).Cells(iLastRow + 1, 1) = "Workbook opened by " & Environ("USERNAME") _
     & " on " & Format(Now(), "dd/mm/yyyy") & " at " & Format(Now(), "hh:nn:ss")
 
  ActiveWorkbook.Save
 
End Sub

Could anyone assist in getting this code to either exclude column 23 from the code as this column doesnt really need to be checked for changes as it will always be the same or making it check that the formula hasnt changed rather than the change of value?

I appreciate any help you are able to provide as I am a beginner with VBA and am trying to learn as i go along.

 

[Yongle]

Try this:

Immediately above

Next iCol

Insert

If c = 22 Then c = 23

 

[JM123]

Try this:

Immediately above

Next iCol

Insert

If c = 22 Then c = 23

Perfect, Thanks for the help