How to run VBA code when cell contents are changed via formula

Question

The code below works fine when I manually update column I. What I need is to know if there is a way to still have this code work when I have column I updated by a formula.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    With Target
        If .Count > 1 Then Exit Sub

        If Not Intersect(Range("I3:I30"), .Cells) Is Nothing Then
            Application.EnableEvents = False
            If IsEmpty(.Value) Then
                .Offset(0, -1).ClearContents
            Else
                With .Offset(0, -1)
                    .NumberFormat = "m/d/yy h:mm"
                    .Value = Now
                End With
            End If

            Application.EnableEvents = True
        End If
    End With
End Sub

Answer 1

Worksheet_Change does not fire in responce to a formula update.

See Excel help for Worksheet_Change

Occurs when cells on the worksheet are changed by the user or by an external link.

You could maybe achieve what you want with the Worksheet_Calculate event.

Assuming you want to put a time stamp next to the cells when those vall values change, try this (in addition to your Change event).

Note the use of the Static variable to track previous values, since Calculate event does nopt provide a Target parameter like Change does. This method may not be robust enough since Static's get reset if you break vba execution (eg on an unhandled error). If you want it more robust, consider saving previous values on another (hidden) sheet.

Private Sub Worksheet_Calculate()
    Dim rng As Range, cl As Range
    Static OldData As Variant

    Application.EnableEvents = False
    Set rng = Me.Range("I3:I30")

    If IsEmpty(OldData) Then
        OldData = rng.Value
    End If

    For Each cl In rng.Cells
        If Len(cl) = 0 Then
            cl.Offset(0, -1).ClearContents
        Else
            If cl.Value <> OldData(cl.Row - rng.Row + 1, 1) Then
                With cl.Offset(0, -1)
                    .NumberFormat = "m/d/yy h:mm:ss"
                    .Value = Now
                End With
            End If
        End If
    Next
    OldData = rng.Value
    Application.EnableEvents = True
End Sub

---

Update

Tested routine on sample sheet, all works as expected

Sample file contains the same code repeated on 25 sheets, and range to time stamp is 10000 rows long.

To avoid repeating the code, use the Workbook_ events. To minimise run time use variant arrays for the loop.

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
    Dim rng As Range
    Dim NewData As Variant

    Dim i As Long
    Static OldData As Variant

    Application.EnableEvents = False
    Set rng = Sh.Range("B2:C10000")  ' <-- notice range includes date column
    NewData = rng

    If IsEmpty(OldData) Then
        OldData = rng.Value
    End If

    For i = LBound(NewData, 1) To UBound(NewData, 1)
        If Len(NewData(i, 1)) = 0 And Len(NewData(i, 2)) > 0 Then
             rng.Cells(i, 2).ClearContents
        Else
            If NewData(i, 1) <> OldData(i, 1) Then
                With rng.Cells(i, 2)
                    .NumberFormat = "m/d/yy -- h:mm:ss"
                    .Value = Now
                End With
            End If
        End If
    Next
    OldData = rng.Value
    Application.EnableEvents = True
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    'Activate date population on cell change
    With Target
        If .Count > 1 Then Exit Sub
        If Not Intersect(Sh.Range("B2:B10000"), .Cells) Is Nothing Then
            Application.EnableEvents = False
            If IsEmpty(.Value) Then
                .Offset(0, 1).ClearContents
            Else
                 'Populate date and time in column c
                With .Offset(0, 1)
                    .NumberFormat = "mm/dd/yyyy -- hh:mm:ss"
                    .Value = Now
                End With
            End If
            Application.EnableEvents = True
        End If
    End With

End Sub