Bob

VB code goes in VB modules, not in worksheet cells.
Rightclick the shet tab, choose "View code", paste the code in the white
module sheet that appears, return to the Excel sheet and test.

HTH. Best wishes Harald

"robert morris" skrev i melding
...
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.