auto fill current time

G

glaw

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.
 
T

T. Valko

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.
 
G

glaw

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?
 
T

T. Valko

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
 
G

glaw

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.
 
G

Gord Dibben

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
 
Top