Need formula/code for "selection falls within set range"

Posted by Derek on January 15, 2002 4:25 AM

I am looking for a formula or macro code that will tell me if a selection of cells falls entirely within a range. For example, if my set range is C4:G5, then I need to know if any of the cells I have selected fall outside of this area. I don't need to know which ones fall outside, just that some do.
I have found a very complex way of acheiving this by splitting addresses and using vlookup, but I am sure there must be a simpler solution.
Any help will be much appreciated
Derek

Posted by Adriana on January 15, 2002 5:30 AM

Macro :-

Dim cell As Range
For Each cell In Selection
If Intersect(cell, Range("C4:G5")) Is Nothing Then
MsgBox "Not all of the selected cells are in the range"
Exit Sub
End If
Next
MsgBox "All of the selected cells are in the range"

Posted by Derek on January 15, 2002 6:26 AM

Re: Thanks Adriana - exactly what I needed (NT)

Posted by Juan Pablo G. on January 15, 2002 7:14 AM

Re: Thanks Adriana - exactly what I needed (NT)

How about this one ? i think it may run faster.

Sub Test()
Dim R1 As Range
Set R1 = Range("C1:D5")

If Union(R1, Selection).Address = R1.Address then
MsgBox "All of the selected cells are in the range"
Else
MsgBox "Not all of the selected cells are in the range"
End If

End Sub

Juan Pablo G.

Posted by Adriama on January 15, 2002 2:55 PM

Yes, that's better (NT)

Sub Test()