Tom,
P5 has not changed since 11:45 but it updates the
time everytime the workbook is closed and reopened. I
defined the name as directed but it still updates

Mike

-----Original Message-----
I would suspect the dOldP5 gets cleared before it runs or
the value in P5
changes.

If dOldP5 is getting cleared, then you might want to
store its value in a
defined name.

go to Insert=Name=Define
in Refersto put in =19 (use the current value of P5)

in the name box put in dOldP5 and click add

Save the file.

Put this in the worksheet code module

'Public dOldP5 As Double

Private Sub Worksheet_Calculate()
dim lVal as Double
lVal =
Application.Evaluate(thisworkbook.Names
("dOldP5").RefersTo)
With Range("P5")
If .Value < lVal Then
With .Offset(0, 1)
.Value = Now
.NumberFormat = "dd mmm yyyy
hh:mm:ss"
End With
Thisworkbook.Names("dOldP5").Refersto
= "=" & .Value
End If
End With
End Sub

Put this in the ThisWorkbook code module:

Private Sub Workbook_Open()
Thisworkbook.Names("dOldP5").Refersto = _
"=" & Sheets(1).Range("P5").Value
End Sub

--
Regards,
Tom Ogilvy


"Mike K" wrote in
message
...
I just noticed a small problem. When I close the sheet
and
reopen it. The time updates

Mike

-----Original Message-----
Since P5 is a calculated cell, I'd use the Calculate
event:

Put this in the worksheet code module

Public dOldP5 As Double

Private Sub Worksheet_Calculate()
With Range("P5")
If .Value < dOldP5 Then
With .Offset(0, 1)
.Value = Now
.NumberFormat = "dd mmm yyyy
hh:mm:ss"
End With
dOldP5 = .Value
End If
End With
End Sub

Put this in the ThisWorkbook code module:

Private Sub Workbook_Open()
dOldP5 = Sheets(1).Range("P5").Value
End Sub

In article ,
"Mike K" wrote:

Basically if cell P5 (my lookup table is converting
it
from a string to a number from 1-19) changes from
whatever
it was to ANYTHING else I need to record the time and
date
in another cell(say Q5). Then when it changes again
record
the new time and date in Q5

.



.