Check to see if cell is in a range

Mister_B

New Member
Joined
Aug 28, 2002
Messages
15
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
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Ivan F Moala

MrExcel MVP
Joined
Feb 10, 2002
Messages
4,209
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

MrExcel MVP
Joined
Aug 20, 2002
Messages
3,355
Hi try this...

Code:
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

New Member
Joined
Aug 28, 2002
Messages
15
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
 

Forum statistics

Threads
1,144,510
Messages
5,724,773
Members
422,577
Latest member
kayth891

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top