This is working great! Thank you for your help.
--
Cathy


"Gord Dibben" wrote:

Wrong type of code for what you want.

The For Each will update every cell whenever a calculation takes place.

Try this sheet_change version.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo enditall
Application.EnableEvents = False
If Not Application.Intersect(Range("a2:a100"), Target) Is Nothing Then
n = Target.Row
If Me.Range("A" & n).Value < "" Then
Me.Range("B" & n).Value = Format(Date, "dd mm yyyy")
Me.Range("C" & n).Value = Format(Time, "hh:mm:ss")
End If
End If
enditall:
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP

On Fri, 16 Jan 2009 13:42:05 -0800, Cathy
wrote:

I'm doing something similar and can't quite get it to work. I used your code
and modified for my spreadsheet. I want col B (date) and col C (time) to
auto entry when someone manually enters an item in column A from a list
(name). But, it's still updating with the current date/time instead of
staying static. Any suggestions? Do I not have the code quite right? Thanks

Private Sub Worksheet_Calculate()
Set r = Range("a2:a100")
For Each a In r
If a.Value < "" And a.Offset(0, -10).Value = "" Then
Application.EnableEvents = False
Cells(a.Row, "b").Value = Date
Cells(a.Row, "c").Value = Time
Application.EnableEvents = True
End If
Next
End Sub