Check to see if cell is in a range

[Mister_B]

I have a range - A1 to A9 - that is not to be
altered. The macro that I'm writing moves
the active cell from wherever it is to the
very first cell in the row. I want to check
to see if the active cell has moved into this
"off limits" range. If so, then the macro
will relocate the active cell to $C$10. If
the active cell is not in this range then
other events will occur.

What VBA code would let me do this?


Thanks for your time and any help will be appreciated,

Dave

 

[Ivan F Moala]

On 2002-08-30 23:20, Mister_B wrote:
I have a range - A1 to A9 - that is not to be
altered. The macro that I'm writing moves
the active cell from wherever it is to the
very first cell in the row. I want to check
to see if the active cell has moved into this
"off limits" range. If so, then the macro
will relocate the active cell to $C$10. If
the active cell is not in this range then
other events will occur.

What VBA code would let me do this?


Thanks for your time and any help will be appreciated,

Dave

Dave lookup in the VBA Editor for help on;
Sheet change event
Application.Intersect

That should get you started.

 

[parry]

Hi try this...

Sub offlimits()

Select Case ActiveCell.Address
Case "$A" To "$A"
Range("c10").Select
Case "A1" To "A9"
Range("c10").Select
End Select
End Sub

--Edit--
mmm this message board doesnt like two $ within quotes so first case $A$1 & $A$9
This message was edited by parry on 2002-08-30 23:51
This message was edited by parry on 2002-08-30 23:52
This message was edited by parry on 2002-08-30 23:54
This message was edited by parry on 2002-08-30 23:55

 

[Mister_B]

Thanks for the replies. I especially liked
Ivan's suggestion to study the "intersect"
concept. I appreciate the code, but learning
what makes the code tick is so valuable.

Thanks a lot.

Dave