VBA to keep a running log of changes to specific cell

[pmcgrath92]

Hello all,

As per subject, I am using a basic VBA code which tracks changes in particular cell(s) - in this example, let's say it is A1. As a preamble: I'm aware that using Tracked Changes could achieve the same result; however (without going into too much detail), this solution works best.

I'm using the following code (adapted for cell A1):

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim strAddress As String
    Dim val
    Dim dtmTime As Date
    Dim Rw As Long
  
    If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
    dtmTime = Now()
    val = Target.Value
    strAddress = Target.Address
  
    Rw = Sheets("Log Sheet").Range("A" & Rows.Count).End(xlUp).Row + 1
    With Sheets("Log Sheet")
        .Cells(Rw, 1) = strAddress
        .Cells(Rw, 2) = val
        .Cells(Rw, 3) = dtmTime
    End With
End Sub

As above, this will log the cell where the value has changed (in this case, $A$1), the value (let's say it changed from YES to NO) & the time of the change - into a sheet named "Log Sheet".

Now, the problem:
This works fine when changes in A1 are typed changes - like the above example, of changing from "YES" to "NO", the sheet would log "NO".
However, the issue arises when the change is a VALUE change as a result of calculation - for example, let's say A1 = B1+C1; if I change the values in B1 and C1, then of course the value of A1 changes; however, since there was no change to the CELL itself (i.e. A1 still equals B1+C1), then there is nothing logged. That is to say, any change in the result of the calculation does not log (i.e. if A1 changed from =4 to =5 as a result of changes to B1 and/or C1), since there was no change to A1=B1+C1.
This also remains true for IF functions - i.e. if A1 cell value changes as a result of an IF function based on other cells, it will not be logged; since the IF function itself did not change.

Therefore, the question:
How can I adapt my VBA in order to log changes in the cell value as opposed to just the cell input?

Thanks in advance!

 

[Joe4]

You are welcome.
Glad you got it working the way you need!