Time Stamp

[Ken]

I have a dynamic stock quote in cell "B2" ... in cell "C2" I want to record
the exact time that the stock price FIRST reachs a certain price. How do I
have this cell record this exact time only the first time the stock is at
this price and not change it later?

 

[ozgrid.com]

Right click on the Sheet and choose View Code. In here paste


Private Sub Worksheet_Calculate()
If Range("B2") = 100 Then Range("C2") = Now
End Sub

 

[Dave Peterson]

I'm not sure what dynamic means in this case, but if it's based on a formula,
you could use something like this worksheet event procedure:

Option Explicit
Private Sub Worksheet_Calculate()

Dim myRngToCheck As Range
Dim myCell As Range
Dim myLimit As Double

Set myRngToCheck = Me.Range("B2")

myLimit = 100

Application.EnableEvents = False
For Each myCell In myRngToCheck.Cells
If myCell.Value2 > myLimit Then
If IsEmpty(myCell.Offset(0, 1).Value) Then
With myCell.Offset(0, 1)
.NumberFormat = "mmm dd, yyyy hh:mm:ss"
.Value = Now
End With
End If
End If
Next myCell
Application.EnableEvents = True

End Sub

If you want to try this, rightclick on the worksheet tab that should have this
procedure. Select View Code and paste this into the new codewindow that opened
(usually on the right).

If that dynamic update means something else that doesn't cause recalculations,
maybe you could tie into what ever updates that cell (a macro that refreshes a
query and does the other work, too????)