NOW() keeps changing

[sigfreund]

I am trying to use now() in a colum to record the time another cell is
changed. Works great until the next cell is changed, instead of
getting a colum of times recording when each cell is entered I end up
with a colum identical times all changing to the time the last cell was
entered. How do I keep the time from changing once my formula enters it
into the cell?
=if(a1<>"",now())

 

[Guest]

The below code pasted into your worksheet code module
will do fine <<Modify Range B6:B20 to your Range>> before testing,,

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("B6:B20"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 1).ClearContents
Else
With .Offset(0, 1)
.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Now
End With
End If
Application.EnableEvents = True
End If
End With
End Sub

 

[sigfreund]

I found a way to keep the time from changing as I enter my data down the
colum. I added a conditional so once a time was entered the formula
went from now() to a direct cell ref to itself.

=IF(L1<>"",IF(K2="",NOW(),K1),"")

this is the formula for cell k1