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

[SPUSHECK]

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?

 

[erik.van.geit]

Hi,

perhaps a doubleclick would do ?

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]

Not quite what you are asking, but...
Ctrl-; will input the current Date into the active cell, Ctrl-: will input the current Time.

 

[DominicB]

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]

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

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