change cell based on selected cell

[RoyR]

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-comfficeffice" /><o> </o>
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-comffice:smarttags" /><st1lace w:st="on"><st1:PlaceName w:st="on">Then</st1:PlaceName> <st1:PlaceType w:st="on">Range</st1:PlaceType></st1lace>("A10").Select
<o> </o>
The following don’t work:
If ActiveCell.Address = ("$A$1:$A$9") <st1lace w:st="on"><st1:PlaceName w:st="on">Then</st1:PlaceName> <st1:PlaceType w:st="on">Range</st1:PlaceType></st1lace>("A10").Select
Or
If InRange(ActiveCell, Range("A1:A9")) <st1lace w:st="on"><st1:PlaceName w:st="on">Then</st1:PlaceName> <st1:PlaceType w:st="on">Range</st1:PlaceType></st1lace>("A10").Select
<o> </o>
I tried variations of this but it is eluding me.
<o> </o>
Thank you for any help.

 

[John Davis]

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-comfficeffice" /><o> </o>
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-comffice:smarttags" /><st1lace w:st="on"><st1:PlaceName w:st="on">Then</st1:PlaceName> <st1:PlaceType w:st="on">Range</st1:PlaceType></st1lace>("A10").Select
<o> </o>
The following don’t work:
If ActiveCell.Address = ("$A$1:$A$9") <st1lace w:st="on"><st1:PlaceName w:st="on">Then</st1:PlaceName> <st1:PlaceType w:st="on">Range</st1:PlaceType></st1lace>("A10").Select
Or
If InRange(ActiveCell, Range("A1:A9")) <st1lace w:st="on"><st1:PlaceName w:st="on">Then</st1:PlaceName> <st1:PlaceType w:st="on">Range</st1:PlaceType></st1lace>("A10").Select
<o> </o>
I tried variations of this but it is eluding me.
<o> </o>
Thank you for any help.

Does this event code help?

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]

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]

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]

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]

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.