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())


--
sigfreund
------------------------------------------------------------------------
sigfreund's Profile: http://www.excelforum.com/member.php...o&userid=32096
View this thread: http://www.excelforum.com/showthread...hreadid=518856

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" wrote:


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())


--
sigfreund
------------------------------------------------------------------------
sigfreund's Profile: http://www.excelforum.com/member.php...o&userid=32096
View this thread: http://www.excelforum.com/showthread...hreadid=518856

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


--
sigfreund
------------------------------------------------------------------------
sigfreund's Profile: http://www.excelforum.com/member.php...o&userid=32096
View this thread: http://www.excelforum.com/showthread...hreadid=518856