How to verify if a range (or a cell) is contained in another range

  • Thread starter Thread starter Nicola M
  • Start date Start date
N

Nicola M

Hi all.
As above written, how can i get this? The issue is the follow: with VBA I
extended conditional formatting with a lot of features, exceeding the normal
3 condition. Now I need this "personal conditional formatting" works only for
the changed cell in a specific range and not in alla worksheet.

Thank you in advance for tips, advice and suggestions.
Nicola
 
From Immediate Window

? Not Application.Intersect(Range("Range1"),Range("Range2")) is Nothing
True << produced answer..
 
Nicola,

One way is with worksheet change where you can capture the address of the
cell that has changed and act accordingly. The code below only executes if
the changed cell is in the range A1 - A100.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
'If Not Intersect(Target, Range("A1:A100")) Is Nothing Then

Application.EnableEvents = False

'do things
Application.EnableEvents = True
End If
End Sub


Mike
 
Sub ProperSubSet()
Dim littleRange As Range
Dim bigRange As Range
Dim r As Range
Set littleRange = Range("A2:D9")
Set bigRange = Range("A1:Z100")
For Each r In littleRange
If Intersect(r, bigRange) Is Nothing Then
MsgBox ("Little not contained with Big")
Exit Sub
End If
Next
MsgBox ("Little contained with Big")
End Sub
 
Back
Top