Prev: VBA Custom Toolbar
Next: When does conditional formating fire?
From: Neal Zimm on 20 Jan 2010 10:59
After I had the same situation, and reading some posts about checking to see
if the cells in one range were contained in another range, I wrote the
function below.

It returns the boolean True or False value, and optionally, if range A is
not entirely within range B, the first failing cell is returned.

Public Function bRngAinRngBF(RngA As Range, RngB As Range, _
Optional FailedRng As Range = Nothing) As Boolean
'True if every cell in RngA is in RngB. > 1 Area is OK in either.
'When False, the 1st cell in RngA not found in RngB is returned in FailedRng.

Dim CellA As Range
Dim IxB As Integer, IxA As Integer


Set FailedRng = Nothing
If RngA Is Nothing Or RngB Is Nothing Then Exit Function

For IxA = 1 To RngA.Areas.Count
For Each CellA In RngA.Areas(IxA)

For IxB = 1 To RngB.Areas.Count
If Not Intersect(CellA, RngB.Areas(IxB)) Is Nothing Then Exit For
'found
Next IxB

If IxB > RngB.Areas.Count Then
Set FailedRng = CellA
Exit Function
End If

Next CellA
Next IxA

bRngAinRngBF = True ' Both loops complete, function is true

End Function


--
Neal Z
 | 
Pages: 1
Prev: VBA Custom Toolbar
Next: When does conditional formating fire?