Thread: Timestamp not calculating when referencing a calculated (IF) cell

I have read dozens of posts regarding McGimpsey solution to the timestamp problem (http://www.mcgimpsey.com/excel/timestamp.html)

His VBA solution works perfectly if you type a value in the reference cell.

However if the reference cell contains a formula then nothing happens.

eg:

Col B: =if(A1>50,1,0) etc . .
Col C: Uses McGimpsey VBA code to update C1 when A1>50

The Col C timestamp only works if I type "1" into Col B. Nothing happens otherwise.

My actual problem is much more complex than shown above - but I hope this illustrates the point.

Any ideas how to overcome this problem.

If the change is the result of a formula then take a look at the calculate event

Hi Greg,

Maybe this will help

http://dmcritchie.mvps.org/excel/event.htm#calculate

From Excel help

Worksheet.Calculate Event
Occurs after the worksheet is recalculated, for the Worksheet object.
Syntax

expression.Calculate

expression A variable that represents a Worksheet object.

Return Value
nothing

Example

This example adjusts the size of columns A through F whenever the worksheet is recalculated.

Private Sub Worksheet_Calculate()
    Columns("A:F").AutoFit
End Sub

What code are you using?

Would this work for you?

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim rngtest As Range
    If Target.Count > 1 Then Exit Sub
    Set rngtest = Range("A5:I10")
    If Not Application.Intersect(Target, rngtest) Is Nothing Then
        With Target
            If .Count > 1 Then Exit Sub
            Application.EnableEvents = False
            With .Offset(0, 1)
                .NumberFormat = "dd mmm yyyy"
                .Value = Date
            End With
            Application.EnableEvents = True
        End With
    End If
End Sub

Here is a macro that should work as you want.
It actually looks at the target and makes sure it is within the whole table. I think it should only looks at column A and B but this is something you could change yourself. If value of the row of the target cell and column C is 1 then the cell of that row and column D will be set to actual date.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngtest As Range
If Target.Count > 1 Then Exit Sub
Set rngtest = Range("A2:C11")
If Not Application.Intersect(Target, rngtest) Is Nothing Then
  Application.EnableEvents = False
  If Cells(Target.Row, "C") = 1 Then
    With Cells(Target.Row, "D")
      .NumberFormat = "dd mmm yyyy"
      .Value = Date
    End With
  End If
End If
Application.EnableEvents = True
End Sub

Sorry about that Greg I though for sure I had the Worksheet_Calcualte event.

I'm not all that sure at how to solve this one. Let me ask the experts how they would approach this.

You'll need to store the current values of the cells off somewhere they can be "checked" against the current values in the watched range(s). So, I'm going to assume we can use column AA to watch A, AE to watch E, and AI to watch I, 26 columns to the right.

Option Explicit

Private Sub Worksheet_Calculate()
Dim CELL As Range

Application.EnableEvents = False
    For Each CELL In Range("A5:A10, E5:E10, I5:I10")
        If CELL.Value <> CELL.Offset(, 26).Value Then
            With CELL.Offset(, 1)
              .NumberFormat = "dd mmm yyyy"
              .Value = Date
            End With
            CELL.Offset(, 26).Value = CELL.Value
        End If
    Next CELL
Application.EnableEvents = True

End Sub

The code as shown does not change any value in column D unless the values in column C and column E no longer match. I see no reason to think they would change. I manually set the column D values back to a few days ago, then saved and closed/opened the file multiple times and the dates did not change.