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.
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

btadams

Well-known Member
Joined
Jan 6, 2003
Messages
1,942
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,942
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
 

Forum statistics

Threads
1,141,427
Messages
5,706,382
Members
421,446
Latest member
rena jhon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top