Mike:

I removed my formula, copied your VB code in Col C. The same problem exists.
Did I follow your instructions correctly?

Bob M.


"Mike H" wrote:

Hi,

First off I don't understand your formula
=IF(C170<1,"",IF(C1700,NOW(),0))

it could be simplified as

=IF(C1701,NOW(),"")

That said, you cant insert a static date with a formula but you could
utilise the worksheet change event. Right click your sheet tab, view code and
paste the code below in. change the range to suit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, Range("c1:c170")) Is Nothing Then
If IsNumeric(Target) Then
Application.EnableEvents = False
If Target.Value 1 Then Target.Offset(, -1).Value = Now()
Application.EnableEvents = True
On Error GoTo 0
End If
End If
End Sub

Mike

"robert morris" wrote:


I have this formula =IF(C170<1,"",IF(C1700,NOW(),0)) in Col B which is
triggered by entering a number i.e., 125 in Col C.

My problem is all previous Rows in Col C change along with the single entry
in the next Row down.

I need the previous dates and times to remain as they were.

What am I missing?

Bob M.