I need help with adding a timestamp for last modified when the cell that changes is based on a formula. I tried the vba below but it does not work

[serpa]

Private Sub Worksheet_Change(ByVal Target As Range)
'Update 20140722
Dim WorkRng As Range
Dim Rng As Range
Dim xOffsetColumn As Integer
Set WorkRng = Intersect(Application.ActiveSheet.Range("I:I"), Target)
xOffsetColumn = 5
If Not WorkRng Is Nothing Then
Application.EnableEvents = False
For Each Rng In WorkRng
If Not VBA.IsEmpty(Rng.Value) Then
Rng.Offset(0, xOffsetColumn).Value = Now
Rng.Offset(0, xOffsetColumn).NumberFormat = "mm-dd-yyyy"
Else
Rng.Offset(0, xOffsetColumn).ClearContents
End If
Next
Application.EnableEvents = True
End If
End Sub

 

[offthelip]

If you check the MS documentation it states:

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

It also states:

This event does not occur when cells change during a recalculation. Use the Calculate event to trap a sheet recalculation.

So the event will NOT trigger because a formula changes it's value. Only if some thing is typed in on the sheet or an external data source changes the value. ( Even in the latter case it doesn't always trggier the worksheet change event)

 

[Joe4]

Welcome to the Board!

To build on what offthelip states, "Worksheet_Calculate" event procedure code is not really good for timestamps because their is no "Target" parameter in "Worksheet_Calculate".
So all that it knows is some cell somewhere on the sheet was recalculated, but it cannot identify which one. So you have no idea what row to add the timestamp too.