How to test if a range is with in another range?

[Mike Mertes]

Specifically, I'm using a select...case statement in a Worksheet_change()
procedure to test which cell is being changed by the user. In this example
you can see why my approach isn't suitable:

Select Target.Address(false,false)
Case "D9" to "D30"
DoSomeStuff
End Select

Alphabetically "D30" occurs before "D9" so this test fails.

I could test for each range individually like "D9","D10","D11",etc... but
that's long and sloppy. There must be an easier way to test if a range is
within or between another range? (BTW, I realize this won't work on a
selection across multiple rows and columns. I have accounted for that.)

TIA
-Mike

 

[Ken Johnson]

Hi Mike,
Will this work?
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target,Range("D930")) is Nothing then
Exit Sub
End If
Do Something
End Sub

 

[Mike Mertes]

I believe it will. Thanks for helping me expand my VB vocabulary

 

[Ken Johnson]

Mike,
Thanks for the feedback and you're welcome. I'm glad I could help.
Ken Johnson