Grabbing the cell value before it is changed (or on cell selection)

[Asterix]

Dear all,

Am using "ByVal Target As Range" to identify cells that have changed, but how do I assign the value in that cell to a variable AFTER the cell has been selected but BEFORE the value has been changed (without assigning variables to and array dim'd by 255 columns and 65536 rows #-)?

[Bob Phillips]

Using the SelectionChange event to catch the target value.

[Asterix]

Cheers, that works nicely except (unless I'm imagining it), e.g....

A1 contains 100 - A2 contains 200 - I select A1, 100 is assigned to a var within the selection change event.

A1 is now the activecell.

I change A1 from 100 to 150 and press return =>

A1 contains 150 - A2 contains 200 - A2 is now the activecell, 200 is assigned to the var within the selectionchange event.

I press undo/ CTRL-Z/ etc...

A1 contains 100 - A2 contains 200 - A2 is now the activecell, 200 remains assigned to the var within the selectionchange event (i.e. I hoped that 150 would have been assigned).

I'll keep on persevering but does that ring a bell? If yes, can I screen it out?

[Bob Phillips]

Oh no, the undo stack is trashed when you run code.

Also, as soon as you select A2 you catch the 200 value as you note, why would you want undo to assign 150, a value it never had , to it?