Hi KSee,

You're welcome and glad I could help.

You brought me to the idea to use mod 3. In a previous mail you write that
there is a fix offset of 3.

Your range starts at column "F", which is column 6. The MOD function returns
the remainder of the devision, so
6 MOD 3 returns 0 because there is no remainder.
For Column G (7), MOD will return 1, H (8) will return 2, I (9) will return
0 and so on.

You want the timestamp in F, I, L ...

If we change e.g. column H, then cells(67, "H"-2) points to cells(67,8-2=6).

Thanks for the compliment, I'm Dutch speaking too.

Wkr,

JP


"KSee" wrote in message
...
Hi JP,
This is fabulous, fantastic! With only 10 lines of code. It is working for
the whole area exactly as I hoped. I am still studying how exactly you did
it
(using Mod 3), but with your explanation I will understand it :) In Dutch
we
say: "Petje af" ("Cap off") or something like that :)
Thanks a lot and have a nice weekend, for me it is already now!
--
KSee


"JP Ronse" wrote:

Hi KSee,

Try following code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim intCount As Integer

Application.EnableEvents = False
ActiveSheet.Unprotect

If (Intersect(Target, Range("F3:CE65")) Is Nothing) = False Then
intCount = Target.Column Mod 3
Cells(67, Target.Column - intCount) = Now
End If

ActiveSheet.Protect
Application.EnableEvents = True

End Sub

Wherever you change something in the range F3:CE65, the IF(INTERSECT is
true.
intcount is the reminder of the columnnumber divided by 3, e.g. changing
range("BP35") =
intcount =68 mod 3 = 2

Traget.column-intcount = 68-2 = column 66.

Hope this helps.

Wkr,

JP


"KSee" wrote in message
...
Hi JP, I hoped that there was a smart solution with 'Offset' or
something
like that because there is a regular offset of 3 colums in the colum
areas
with data as well as in the (locked) cell which kept the updated date,
also a
offset of 3 colums ....
Now I get a error on 'then' in the code. I have implemented your lines
but
it seems a bit of a mesh now, pls help...

Dim r1 As Range, r2 As Range, r41 As Range
Set t = Target
Set r1 = Range("F3:H65")
Set r2 = Range("I3:K65")
Set r41 = Range("F67")
Set r42 = Range("I67")
If Intersect(t, r1) Is Nothing Then Exit Sub
If (Intersect(Target, Range("F3:H65") is nothing) is false then
Range("F67") = Now
elseif (Intersect(target, range("I3:I65") is nothing) is false then
Range("I67") = Now
End If
Application.EnableEvents = False
ActiveSheet.Unprotect
r41.Value = Now
r42.Value = Now
ActiveSheet.Protect
Application.EnableEvents = True
End Sub

--
KSee


"JP Ronse" wrote:

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