No luck, Alan. Here's the situation: if a cell in column V has a currency
amount in it (which is the result of a formula involving other columns), I
want a date stamp in the corresponding cell in column U. Which is why I
wonder if the programming below, which indicates that column V should have
"Nothing" is correct? My original formula for column U, for reference, was
=IF(ISNUMBER(V10), NOW(), ""), but NOW produces a constantly updated date
every time you open the document, which I don't want.

"Alan" wrote:

Try this,

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

You have

If Not Application.Intersect(Target, Range("V10:V499")) Is Number Then

It should be

If Not Application.Intersect(Target, Range("V10:V499")) Is Nothing Then

Regards,
Alan.

"EMW103" wrote in message
...
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?