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!

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


"glaw" wrote in message
...
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!

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

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


"glaw" wrote in message
...
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!

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


"glaw" wrote in message
...
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" wrote:

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


"glaw" wrote in message
...
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!

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!


"T. Valko" wrote:

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


"glaw" wrote in message
...
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" wrote:

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


"glaw" wrote in message
...
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!

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

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.