Prev: Microsoft Responds to the Evolution of Online Communities
Next: Recognizing an Unstable Result
From: Ken on 4 May 2010 22:35
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?
--
Ken
From: ozgrid.com on 4 May 2010 23:00
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


--
Regards
Dave Hawley
www.ozgrid.com
"Ken" <Ken(a)discussions.microsoft.com> wrote in message
news:32B87CC8-6276-4783-952C-C9D84786C3E4(a)microsoft.com...
>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?
> --
> Ken

From: Dave Peterson on 5 May 2010 08:15
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????)



Ken wrote:
>
> 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?
> --
> Ken

--

Dave Peterson
 | 
Pages: 1
Prev: Microsoft Responds to the Evolution of Online Communities
Next: Recognizing an Unstable Result