Right click on the sheet tab and select view code

At the top of the module
In the left dropdown select Worksheet
in the right dropdown select Change (not selection change)

this will put in a declaration for the change event.

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

This event will fire everytime you complete the edit of a cell. So in this
routine you would test for the cell being changed that required a timestamp.
Assume a change in column B requires a timestamp in column A of the same row
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count 1 Then Exit Sub
On Error GoTo ErrHandler
If Target.Column = 2 Then
Application.EnableEvents = False
If Not IsEmpty(Target) Then
Cells(Target.Row, 1).Value = Now
Cells(Target.Row, 1).NumberFormat = "mm/dd/yyyy hh:mm"
Else
Cells(Target.Row, 1).ClearContents
End If
End If

ErrHandler:
Application.EnableEvents = True
End Sub


--
Regards,
Tom Ogilvy



"Axeman" wrote in message
...

Hi

I just came across this and wanted to ask about a sheet I am trying to
prepare, how do I apply the following you mentioned

"Columns(1) = Columns(1).Value"

we have a sheet here that we are using the NOW() function to add the time
and date that an entry is made, however every time a new entry is made on
another row it affects every time and date recorded in the time/date column
to change with it!.

Where exactly in excel do I apply this "Columns(1) = Columns(1).Value"
that you mention?

Regards

Axe

----- Doug Glancy wrote: -----

dev,

Not sure I understand, but the following will convert all the
formulas in
column 1 to values:

Columns(1) = Columns(1).Value

If you apply this after you insert your new row with the Now()
formula, then
it will "freeze" the date and time. If you don't want to do the
whole
column, you could use the same type of syntax with a range:

range("A1") = range("A1")

hth,

Doug

"dev" wrote in message
om...
I am trying to create macros that will auto fill cells. I have made
it
ok to the point of inserting new row and filling in the info i want
but in the column where i have the date it keeps changing all the
previous entries to the current entries value. It happens only in
this
column with the date. I am using the value =now() , is there
another
value i can enter to correct this? Thanks ,,Dev