date/time to show in a cell when I hit "any specific&qu

SPUSHECK

New Member
Joined
Sep 22, 2006
Messages
6
I have conditional Formating in cloum A. It is green and changes to no color when I enter anything into the cell. (Green means that the task has not been addressed and No color means it has). Rather than have to enter the date and time into each cell I would like for the cell to "Auto Fill" the "NOW" date and time when I hit any key or a specific key, like the space bar. I need a formula and I need to know if I need to cancel the Auto formating to use the formula?
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi,

perhaps a doubleclick would do ?

Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Column <> 1 Then Exit Sub
Target = Now
End Sub
http://www.cpearson.com/excel/events.htm
TO INSTALL IN SHEET CODE WINDOW:
1. right click the "Name Tab" of sheet you want code to work in
2. Select "View Code" in drop down menu
3. VBE window will open ... paste code in and exit VBE


kind regards,
Erik
 

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
Not quite what you are asking, but...
Ctrl-; will input the current Date into the active cell, Ctrl-: will input the current Time.
 

DominicB

Well-known Member
Joined
Oct 3, 2005
Messages
1,569
Good evening SPUSHECK

This functionality already exists in Excel - but not in the same cell. Press Ctrl + ; and the date will be pasted into the cell, press Ctrl + Shift + ; and the time will be pasted into the cell. Is this of any use to you?

HTH

DominicB
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
I need to know if I need to cancel the Auto formating to use the formula
just noticed this last part
you don't need to change the formatting
none of these systems change the formats

an enhancement of the code
if you want to erase (1 doubleclick) or change (+ another) the date which is currently in the cell

Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Column <> 1 Then Exit Sub
Cancel = True
Target = IIf(Target <> "", "", Now)
End Sub
 

Forum statistics

Threads
1,137,366
Messages
5,681,068
Members
419,950
Latest member
BeckiJae

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