Excel VBA Intersect Method for Range
In Excel VBA, Intersect Method is used to return the intersection Range (the common area in the above picture).
Syntax of Intersect Method
expression .Intersect(Range1, Range2, …)
The intersection Range must be at least 2, in order to find the intersection area.
If intersection area exists, Intersection Method returns a Range
If intersection area does not exist, Intersection Method causes a Runtime Error, which can be avoided using “Is Nothing” keyword, meaning “nothing” returns.
To add a Macro action if intersection exists:
If Not Intersect(Range1, Range2, ...) is nothing Then Your code... End If
To add a Macro action if intersection does not exist:
If Intersect(Range1, Range2, ...) is nothing Then Your code... End If
Example of Intersect Method
The below example checks if Target Range lies in column A, if it is true and value is >100, a message box pops up.
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A:A")) Is Nothing And Target.Value > 100 Then
MsgBox ("Column A value >100")
End If
End Sub
You can refer to the below link if you don’t understand Worksheet_Change event.
Excel VBA Worksheet.Change Event
Intersection of single Range
Since Intersect Method is used for at least 2 Cells, we cannot apply Intersect Method if you try to verify if the single target Cell falls into our action range.
To check single cell , we can simply use Address Property. The below example check if target Range is at G6.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(rowabsolute:=False, columnabsolute:=False) = "G6" Then
MsgBox ("This cell is G6")
End If
End Sub
Address Property returns absolute cell address with dollar sign, such as $G$6, parameters
(rowabsolute:=False, columnabsolute:=False)
remove the dollar sign and return “G6”. (of course you can also use $G$6 in the condition instead)
Outbound References
http://msdn.microsoft.com/en-us/library/office/aa195772%28v=office.11%29.aspx
http://www.ozgrid.com/VBA/vba-intersect.htm