How are the changes in A2:C2 being made?

The Sheet_Change code works only when a manual change is made in any of
A2:C2 cells.

I tested the code you posted under those circumstances and is OK.

If A2:C2 are formula-dreived values then you must use a Sheet_Calculate
event.

Private Sub Worksheet_Calculate()
code
End Sub


Gord Dibben MS Excel MVP

On Thu, 26 Mar 2009 18:41:01 -0700, DataGuy
wrote:

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?