automatically detecting changes in a cell

A

Antonio Duarte

Every time the user changes the contents of cell A1, I would like to
automatically write down in cell A2, the date and time when A1 was changed.
How can it be done?
 
A

Antonio Duarte

The problem with
=IF(A1="","",IF(B1="",NOW(),B1))
is: if A1 just holds "abc" and I change it to "def", the timestamp in B1
will NOT change!
so, I tryed:
=IF(A1="","",NOW())
It works exactly as I want, BUT...
if I have in B1 the formula =IF(A1="","",NOW())
and in D3 the formula =IF(C2="","",NOW())
when I change A1, BOTH cells B1 AND D3 change simultaneously (in a situation
where only B1 was supposed to change)
Any clue?
 
J

JE McGimpsey

Yup. That's why the other function was constructed the way it was.

NOW() is a volatile function, so every time the worksheet is calculated,
any formula containing NOW() will be calculated, and any cell displaying
the results of NOW() will be updated.

Try the macro route.
 
A

Antonio Duarte

HI.
The macro works almost fine...
After creating the macro (using exactly the same range you have in the
example), I did the following test im my worksheet:
Selected cells A8 to A10
Copyed them
Pasted these cells to the range A2 to A4
and the associated dates didn't change.
Checking the code I noticed the line
If .Count > 1 Then Exit Sub
and I tryed to comment it, with no changes in the final result.
So, is there any reason for the existence of this line of code?
Do you have any idea on what is happening?
Tks.
 
J

JE McGimpsey

Yes, the line of code exists because there is no event which returns
just the cell(s) that were changed. Instead, Target returns the
*selected* cells when the change was made.

Thus Worksheet_Change can't tell whether you pasted into all of the
cells, or simply had a range selected and changed one cell.

To to that would require that you do something like saving the selected
range in a variable (or another range) when it was selected. Then when
the worksheet_change() fires, you could compare the new values to the
stored values, time-stamping each cell that changed. Note that since the
selection change occurs before the Worksheet_Change event, there's the
need to make that storage somewhat persistent.
 
Top