Date/Time "Current" Auto fill when next cell edite

SPUSHECK

New Member
Joined
Sep 22, 2006
Messages
6
How to get a cell to auto fill with the "Current Date and Time" and be activated by the entry of text into the cell next to it.
I keep a business journal for each account and when I start typing in a cell I want the cell to the left of my notes to indicate the date and time that I typed the text.
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

btadams

Well-known Member
Joined
Jan 6, 2003
Messages
1,936
Copy/paste this code in the worksheet (right-mouse click on the worksheet tab and choose View Code):

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    Application.EnableEvents = False
    Target.Offset(0, 1).Value = Now
    Application.EnableEvents = True
    On Error GoTo 0
    
End Sub
 

SPUSHECK

New Member
Joined
Sep 22, 2006
Messages
6
Date and Time Auto Fill. Need more help

THANK YOU: This is a GREAT Formula:
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Application.EnableEvents = False
Target.Offset(0, 1).Value = Now
Application.EnableEvents = True
On Error GoTo 0

End Sub

However, two remianing issues:
1. The date and time appear in the cell to the right and I need them to appear in the cell to the left of the active cell.
2. I only want it active in certain cells. When I try to past the formula into 1 cell rather than apply it to the entire page, it does not work?

Any suggestions?
 

btadams

Well-known Member
Joined
Jan 6, 2003
Messages
1,936
The code below, when pasted into the worksheet module, will only enter the date (on the left of the active cell) if the active cell is in column C.

Change Range("C:C") to whatever range you want the macro to work on.


Code:
Private Sub Worksheet_Change(ByVal Target As Range) 
    On Error Resume Next 
     If Not Intersect(Target, Range("C:C")) is Nothing Then
          Application.EnableEvents = False 
          Target.Offset(0, -1).Value = Now 
          Application.EnableEvents = True 
    End If
    On Error GoTo 0 
    
End Sub
 

SPUSHECK

New Member
Joined
Sep 22, 2006
Messages
6

ADVERTISEMENT

Thank you, I am almost their. I changed the formula to the below and the problem is that the date always works but once I get to row 35 the time stops being entered.
Any suggestions?

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Not Intersect(Target, Range("B32:B99")) Is Nothing Then
Application.EnableEvents = False
Target.Offset(0, -1).Value = Now
Application.EnableEvents = True
End If
On Error GoTo 0

End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,112,885
Messages
5,543,028
Members
410,583
Latest member
gazz57
Top