Hi KSee,

Your code is only testing one range (F3:F65), you have to extend it for the
other ranges.

if (intersect(target, range("F3:F65") is nothing) is false then
range("F67")=now
elseif (intersect(target, range("I3:I65") is nothing) is false then
range("I67")=now
....

end if

Wkr,

JP

"KSee" wrote in message
...
Here I am back again. I was satisfied a bit to early because I can't make
it
work for the whole sheet :( The code below is working for the first day of
the month.. What should I change to the code if I wanted to work it also
for
the next 11 month and the extra colums?
So, when there is a change in colums "F3:H65", the date in cell "F67" must
be updated. When there is a change in colums "I3:K3", the date in cell
"I67"
must be updated. This continues until the last range: when there is a
change
in colums "CC3:CE65", the date in cell "CC67" must be updated.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r1 As Range, r41 As Range
Set t = Target
Set r1 = Range("F3:F65")
Set r41 = Range("F67")
If Intersect(t, r1) Is Nothing Then Exit Sub
Application.EnableEvents = False
ActiveSheet.Unprotect
r41.Value = Now
ActiveSheet.Protect
Application.EnableEvents = True
End Sub

Any help is very appreciated!
--
KSee


"KSee" wrote:

Thanks again Dave for your help and clear explanation. Now the updated
date
and time is stored and kept (also when I reopen the file) in the right
cell
when a day of a month in one of the concerning colums has changed. Just
like
I wanted!
--
KSee


"Dave Peterson" wrote:

Application.enableevents = false
should go before your code changes something. It tells excel to stop
looking
for changes to the worksheet (actually to stop looking for lots of
things). You
don't want the change your code makes to call itself.

So you have that "Application.enableevents = false" before any code
that changes
something.

Then you make the change(s) and finally, you end with
"Application.enableevents
= True". This "true" tells excel that you want it to go back to
monitoring all
the triggers that can fire these events. So the next change the user
(you???)
make to the worksheet will cause that event to fire once more.



KSee wrote:

Yes! Thank you Dave. I put in the line 'Application.EnableEvents =
True' like
you proposed and changed 'r2.Value = Date' to 'r2.Value = Now'
because I
wanted also the time stamp.
Is it not necessary to end with ''Application.EnableEvents = False'
or
something like this. I am not familiar with programming but saw this
line in
other programs.
Thanks again Gary and Dave!
--
KSee

"Dave Peterson" wrote:

Did you put the code in the correct worksheet module?
Rightclick on the worksheet tab that should have this behavior.
Choose View
Code and paste into the newly opened code window (usually on the
right).

While you were testing, did you turn events off and fail to turn
them on?

Open the VBE
hit ctrl-g to see the immediate window
type this
Application.EnableEvents = True
and hit enter.

Then back to excel to test.

KSee wrote:

What is wrong (see below) No date as result in "F67"
--
KSee

"KSee" wrote:

I have merged the 3 cells per day at the end of the colums to
1 cell to
create enough space for the date which should be recorded in
this cell
--
KSee


"Gary''s Student" wrote:

What is F67 merger with??
--
Gary''s Student - gsnu200902


"KSee" wrote:

Thank you Gary. I have the merged cell F67 formatted as
"date time" but no
resulting date in this cell. Below wahta I changed in your
program. Should I
fill in also 'set t = Target' ?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r1 As Range, r2 As Range
Set t = Target
Set r1 = Range("F3:H65")
Set r2 = Range("F67")
If Intersect(t, r1) Is Nothing Then Exit Sub
Application.EnableEvents = False
r2.Value = Date
Application.EnableEvents = True
End Sub
--
KSee


"Gary''s Student" wrote:

Here is a very simple example that you can adapt.

Say we have a data entry area from G2 thru G65. If the
user enters data in
this area, we want the date to be recorded in G66. Put
the following event
macro in the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r1 As Range, r2 As Range
Set t = Target
Set r1 = Range("G2:G65")
Set r2 = Range("G66")
If Intersect(t, r1) Is Nothing Then Exit Sub
Application.EnableEvents = False
r2.Value = Date
Application.EnableEvents = True
End Sub


Because it is worksheet code, it is very easy to install
and use:

1. right-click the tab name near the bottom of the window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with
it.

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm


--
Gary''s Student - gsnu200902


"KSee" wrote:

Hi there, hope somebody can help me!
I am puzzling with a problem: I have a excel 2003 wb
with sheets where the
cells of 3 colums are representing the results of 1
day. In fact colums F:H
do have the results of day 1 of month January to
December. Colums CC:CE the
results of day 31 of the month January to December. The
cells where one can
put in or change the numeric data is the range:
F2:CE65. Under each day, (3
merged colums of that day), I like to have the last
update when someone has
changed the data in one of the 3 'day'colums. The
problem I encounter is that
each time I reopen the workbook the 'Last update' has
changed while there was
no change in the 3 colums. Also the 'Last update' in
other sheets in my
workbook have changed but I want only the last updates
per 'day' and sheet.
Tnx in advance!

--

Dave Peterson


--

Dave Peterson