Folks, I had the exact same question yesterday. I tried the following code:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("V10:V499")) Is Number Then
Range("U10:U499") = Now
End If
End Sub

Something seems to be wrong, though. Can anyone troubleshoot this for me?

"ShePink77" wrote:

Alan, I did get the formula to work the trick was to save my spreadsheet and
then reopen. One last question how do I copy and paste this formula to all
of column E? For example: If I make any update in a specific row only the
date will auto stamp in column E of that same row.

"Alan" wrote:

Hard to say. This an event code and it will only work if the cells are
physically changed, ie the cell is altered directly. It won't work by the
result of a formula in the cell. If you want that to happen you need to
include the cells that the formula refers to.
Did you get at any stage an error message that opened the VB debugger? If
you did, resolve it, save the file and reopen it,
Regards,
Alan.
"DataGuy" wrote in message
...
It is funny that I found a post of the exact thing I need...posted on the
exact same day. Alan and Gord - I have followed your direction and posted
the code in VB but, nothing is appearing when I make a change to cells A2
to
C2 (my range is a little different). I have edited the code to suit my
needs. What am I doing wrong? My data is in cells A2:C2 and I want the
recorded change to post in D2.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("A2:C2")) Is Nothing Then
Range("D2") = Now
Application.EnableEvents = True
End If
End Sub

"Gord Dibben" wrote:

Easiest method to add event code to a sheet is right-click on the sheet
tab
and select "View Code"

Copy/paste the code into that module.

Edit if desired then Alt + q to go back to the Excel window.


Gord Dibben MS Excel MVP

On Thu, 26 Mar 2009 12:49:07 -0700, ShePink77
wrote:

Alan, thanks so much for the help. I have found the Alt F11 but where
do I
copy and paste the below formula once the VBA Project window is open?

"Alan" wrote:

You can't do it with a formula because the NOW() function will update
every
time the workbook calculates, not just when you alter B1:D1
This code will do it,

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("B1:D1")) Is Nothing Then
Range("E1") = Now
Application.EnableEvents = True
End If
End Sub

To enter this,
Press Alt and F11 to open the VB editor
On the top left you'll see VBAProject with your filename in brackets
Expand that and double click Sheet1 or whatever sheet you want to do
this on
Copy and paste the code into this new window, (watch or text wrap in
the
email, there should be seven lines)
Press Alt and F11 again to close the VB editor
Save the file,

Regards,
Alan.


"ShePink77" wrote in message
...
I am creating a spreadsheet and would like to know if I am working in
row 1
and I make add, update or change informaiton in cells B1, C1 or D1
is it
possible to auto stamp the time and date of the add, update or
change in
cell
E1? If so can you help me understand how to write the formula?