change cell based on selected cell

RoyR

Board Regular
Joined
Jan 25, 2012
Messages
62
I have a macro that deals with data starting from cell A10 onward but cells A1 to A9 are off limits. I’m looking for a way to jump to A10 in case the wrong cell was selected.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p> </o:p>
This works if A1 is selected but it would be sloppy to repeat this 8 more times for cells A2 to A9.
If ActiveCell.Address = ("$A$1") <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-com:eek:ffice:smarttags" /><st1:place w:st="on"><st1:PlaceName w:st="on">Then</st1:PlaceName> <st1:PlaceType w:st="on">Range</st1:PlaceType></st1:place>("A10").Select
<o:p> </o:p>
The following don’t work:
If ActiveCell.Address = ("$A$1:$A$9") <st1:place w:st="on"><st1:PlaceName w:st="on">Then</st1:PlaceName> <st1:PlaceType w:st="on">Range</st1:PlaceType></st1:place>("A10").Select
Or
If InRange(ActiveCell, Range("A1:A9")) <st1:place w:st="on"><st1:PlaceName w:st="on">Then</st1:PlaceName> <st1:PlaceType w:st="on">Range</st1:PlaceType></st1:place>("A10").Select
<o:p> </o:p>
I tried variations of this but it is eluding me.
<o:p> </o:p>
Thank you for any help.
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

John Davis

Well-known Member
Joined
Sep 11, 2007
Messages
3,457
I have a macro that deals with data starting from cell A10 onward but cells A1 to A9 are off limits. I’m looking for a way to jump to A10 in case the wrong cell was selected.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p> </o:p>
This works if A1 is selected but it would be sloppy to repeat this 8 more times for cells A2 to A9.
If ActiveCell.Address = ("$A$1") <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-com:eek:ffice:smarttags" /><st1:place w:st="on"><st1:PlaceName w:st="on">Then</st1:PlaceName> <st1:PlaceType w:st="on">Range</st1:PlaceType></st1:place>("A10").Select
<o:p> </o:p>
The following don’t work:
If ActiveCell.Address = ("$A$1:$A$9") <st1:place w:st="on"><st1:PlaceName w:st="on">Then</st1:PlaceName> <st1:PlaceType w:st="on">Range</st1:PlaceType></st1:place>("A10").Select
Or
If InRange(ActiveCell, Range("A1:A9")) <st1:place w:st="on"><st1:PlaceName w:st="on">Then</st1:PlaceName> <st1:PlaceType w:st="on">Range</st1:PlaceType></st1:place>("A10").Select
<o:p> </o:p>
I tried variations of this but it is eluding me.
<o:p> </o:p>
Thank you for any help.

Does this event code help?

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Target, Range("A1:A9")) Is Nothing Then
Range("A10").Select
End If

End Sub
 

RoyR

Board Regular
Joined
Jan 25, 2012
Messages
62
I'll try some variations of that code. The cells are not blank. The do contain data also. Just not data that is to be touched. I should have mentioned that in my post. Thank you for the tip. I will see what I can do with it.
 

John Davis

Well-known Member
Joined
Sep 11, 2007
Messages
3,457
I'll try some variations of that code. The cells are not blank. The do contain data also. Just not data that is to be touched. I should have mentioned that in my post. Thank you for the tip. I will see what I can do with it.


The code as provided does not check if the cells are blank. If any cells in Range("A1:A9") are clicked it will select A10 instead. Hope that is what you were looking for. Please note that the code should go in the worksheet event module.
 

RoyR

Board Regular
Joined
Jan 25, 2012
Messages
62
That is a nice piece of code that works great. At first I tried to use it in a macro and it did not work. But I placed it where you said and now all is fine.

Thank you for your assistance.
 

John Davis

Well-known Member
Joined
Sep 11, 2007
Messages
3,457
That is a nice piece of code that works great. At first I tried to use it in a macro and it did not work. But I placed it where you said and now all is fine.

Thank you for your assistance.

You're welcome. Glad it helped out, and thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,035
Messages
5,622,332
Members
415,894
Latest member
silverhaze

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