Need cell address

[Ferloft]

Hello,

I am using the following code to call a macro whenever one of the key cells is changed

Please Login or Register  to view this content.

I would like a to be the cell address of the cell that changed. I then need to know how to use the variable a within my macro.

If that doesnt make sense I can try to explain more.

Thanks.

[HaHoBe]

Hi, Ferloft,

what´s Macro1 purpose as it is called prior to setting a? Usually you refer to the cells from the sheet the event is called from:

Please Login or Register  to view this content.

Ciao,
Holger

[patel45]

Please Login or Register  to view this content.

[Ferloft]

I just added a in there I have no idea if thats where it should actually go. Essentially whenever one of the cells in the KeyCells range is changed I want Macro1 to run. However at the end of Macro1 I would like the last step to be select the cell below whichever cell was changed.

patel: It didn't like me taking out the .Address part of finding the intersect. When I put it back in it still didn't set a to the cell that changed.

[HaHoBe]

Hi, Ferloft,

maybe like this

Please Login or Register  to view this content.

Ciao,
Holger

[nilem]

Hi Ferloft
I try to guess
maybe something like

Please Login or Register  to view this content.

[Ferloft]

That one seems to be working Holger, thanks! I will mark the thread as solved and if I run into any problems I'll make sure to come back.

Just out of curiosity, why do you turn off events while selecting the cell?

[HaHoBe]

Hi, Ferloft,

a habit of mine from the days of using Excel2000 (it could lead to endless loops there - better excuse than stating I just forgot what event I was handling ): this will not get the event fired in case any cursor movement (SelectionChange) or action (Change) has taken place. May be omitted here (both of the lines).

Ciao,
Holger