checking if user selected cells are within a specified range of ce

[Guest]

I am expecting the user to select 1 or more not necessarily contiguous cells
and then operate on those cells. But first, I want to check that all selected
cells are within a specified cell range, say, $a$11:$a$200. Two questions:

1) how do I loop thru the user selected cells?

2) is there a way to do that range check mentioned above without looping
thru the selected cells?

Thanks a lot.

 

[Guest]

Sub rockh()
Set r = Range("A1:Z100")
For Each rr In Selection
If Intersect(rr, r) Is Nothing Then
MsgBox (rr.Address & " not in range")
Exit Sub
End If
Next
MsgBox ("all selected cells are in range")
End Sub

 

[Guest]

You can use intersect to find the common area of 2 ranges...

Sub ColourSubRange()
Dim rngIntersection As Range

On Error Resume Next
Set rngIntersection = Intersect(Range("A11:A200"), Selection)
On Error GoTo 0

If Not rngIntersection Is Nothing Then
rngIntersection.Interior.ColorIndex = 34

End Sub

 

[Jim Cone]

Sub WhereAreWe()
Dim rngAll As Excel.Range
Dim rngArea As Excel.Range
Dim blnOutside As Boolean

' Establish the boundaries...
Const rTop As Long = 10
Const rBott As Long = 100
Const cLeft As Long = 5
Const cRight As Long = 15
Set rngAll = Selection

For Each rngArea In rngAll.Areas
Select Case True
Case rngArea.Row < rTop
blnOutside = True
Case rngArea.Rows(rngArea.Rows.Count).Row > rBott
blnOutside = True
Case rngArea.Column < cLeft
blnOutside = True
Case rngArea.Columns(rngArea.Columns.Count).Column > cRight
blnOutside = True
End Select
If blnOutside Then
MsgBox "Oops"
Exit For
End If
Next
Set rngArea = Nothing
Set rngAll = Nothing
End Sub
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"rockhammer"
<[email protected]>
wrote in message
I am expecting the user to select 1 or more not necessarily contiguous cells
and then operate on those cells. But first, I want to check that all selected
cells are within a specified cell range, say, $a$11:$a$200. Two questions:

1) how do I loop thru the user selected cells?
2) is there a way to do that range check mentioned above without looping
thru the selected cells?
Thanks a lot.

 

[Guest]

Hi Folks, thanks a lot for all your prompt input. I learnt something new from
each of your replies.

In the end, I settled on the following:

++++++++++++++++++++++
Dim iArea As Range
Dim isOutsideCols As Boolean
Dim isOutsideRows As Boolean

isOutsideCols = False
isOutsideRows = False
For Each iArea In Selection.Areas
Select Case True
Case iArea.Row < row1st
isOutsideRows = True
Case iArea.Rows(iArea.Rows.Count).Row > rowLast
isOutsideRows = True
Case iArea.Column <> colUpdated
isOutsideCols = True
Case iArea.Columns(iArea.Columns.Count).Column <> colUpdated
isOutsideCols = True
End Select
If isOutsideCols Or isOutsideRows Then Exit For
Next

If isOutsideCols Then
MsgBox "Please select cell(s) within the 'Updated' column."
ElseIf isOutsideRows Then
MsgBox "Please select cell(s) among the list of stocks."
Else
Selection.Value = Now
End If
++++++++++++++++++++++