Excel - auto fill current time

Asked By gla on 24-Nov-07 06:27 PM
I need help with this one, I can't find (figure out) the answer myself.
I have a worksheet that use at work, I want to automatically place the
current time into a cell, whenever I place data into the cell 3 rows up. I
want this 'time cell' to automatically update every time I enter data into
the cell 3 rows up. I already know the shortcut to add time to a cell. That's
not what I'm looking for here. Please advise.
--
Thanks very much, Appreciate you!




T. Valko replied on 24-Nov-07 07:07 PM
So, if you enter something in A1 then you want A4 to update with the time?

Try this event macro:

Right click the sheet tab and select View code. Paste the code below into
the window that opens.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1")) Is Nothing Then
With Target
If .Value <> "" Then
Range("A4").Value = Format(Now, "h:mm AM/PM")
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


Close the window to return to Excel.


--
Biff
Microsoft Excel MVP
gla replied on 24-Nov-07 08:09 PM
THANK YOU VERY MUCH!

It worked great.
Now for a follow up question. I need to repeat this process across 14
consecutive columns. What is the easiest way to do this?
--
Thanks very much, Appreciate you!
T. Valko replied on 24-Nov-07 10:23 PM
Assume the 14 columns are A:N

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:N1")) Is Nothing Then
With Target
If .Value <> "" Then
Target.Offset(3, 0).Value = Format(Now, "h:mm AM/PM")
End If
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub


--
Biff
Microsoft Excel MVP
gla replied on 30-Sep-08 08:57 PM
I now have an additional problem to add to this. I need to add a second line
of auto complete time in the same sheet.  I need to auto add time to
(B52:O52), when I add data to (B51:O51). I've tried to do this myself but I
don't know how. Please help.
--
Thanks very much, Appreciate you!
Gord Dibben replied on 01-Oct-08 04:35 PM
Editing Biff's code...........

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("B51:O51")) Is Nothing Then
With Target
If .Value <> "" Then
Target.Offset(1, 0).Value = Format(Now, "h:mm AM/PM")
End If
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub


Gord Dibben  MS Excel MVP

On Tue, 30 Sep 2008 17:57:00 -0700, glaw <glaw@>