The entire Excel application is under VBA Application Object. Moreover, Excel provides different application methods like the VBA Intersect method to return a Range object corresponding to the rectangular intersecting point of two or more ranges. Furthermore, we practically use the VBA Intersect method to filter or interact with data that has intersections from other areas, such as Owner, Dates, Locations, etc. In this article, we will see 4 practical examples of using the VBA Intersect method in Excel.
Excel VBA Intersect Method: 4 Practical Examples
In math or geometry, a VBA Intersect occurs when two or more lines or areas cross one another. The resulting shared point or area is known as the intersection point or area. We can acquire the intersection cell addresses, select them, and customize them. Further, we can also measure and highlight the intersection area in Excel. Before diving into the examples, let’s take a dataset that represents US addresses and their zip codes for demonstration.
Example 1: Display Intersection Point Address Utilizing VBA Intersect Method in Excel
The first example aims to replace the Variable with a Variant. Also, we use the Intersect method to assign a value to this variable. We will test only 2 ranges in this instance. However, you can take as many ranges as required. Let’s follow the below instructions to implement the VBA code perfectly.
Step 1:
- Initially, go to the Developer tab and click Visual Basic.
- Subsequently, the Visual Basic window pops up.
- Meanwhile, tap Insert and then Module to create a module box.
Step 2:
- Further, type the following VBA Intersection code in the module box.
- Also, include 2 ranges from the dataset.
Sub Dis_Inter_Address()
 Dim MyOutput As Variant
 MyOutput = Intersect(Range("C5:C9"),
Range("B7:C7")).Address
 MsgBox MyOutput
End Sub
- Next, press the F5 key or the green Run icon.
- Thus, we obtain the desired intersection cell address in a message box.
- Dim MyOutput As Variant: Here, we declare a Variant namely MyOutput instead of a Variable.
- MyOutput = Intersect(Range(“C5:C9”), Range(“B7:C7”)).Address: Now, in the Intersect formula, we put 2 ranges (C5:C9) and (B7:C7) in the declared Variant and add the Address object to get the intersect cell address.
- MsgBox MyOutput: Finally, the MsgBox application displays the intersect point address.
Example 2: Highlight Intersection Cell Using VBA Application.Intersect Formula in Excel
The objective of the second example is to select the intersecting cell in our dataset. For implementation, we will use the Select application with the Intersect function. Let’s follow the easy steps.
Steps:
- Like the previous example, create a Module box.
- There, input the following VBA Intersect formula.
Sub Highlight_Inter_Pt()
 Intersect(Range("C5:C9"), Range("B7:C7")).Select
End Sub
- Here, the Intersect function determines the intersection point between the 2 ranges. Further, the Select application returns a selection in the dataset.
- After pressing the green Run icon, close the window tab.
- Thus, we get the intersection cell of the ranges selected.
Example 3: Remove Intersection Cell in Excel
In this example, we will thoroughly delete the information in the intersection cell of the specified range using the Intersect function and ClearContents application. To do so, we need to see the below procedure.
Steps:
- Firstly, create a Module box and write the VBA code.
- See the below picture for a better understanding.
Sub Rem_Inter_Point()
 Intersect(Range("C5:C9"), Range("B7:C7")).ClearContents
End Sub
- Afterward, press the F5 key or the Run icon and close the tab.
- Hence, we remove the data of the intersection cell C7.
Example 4: Customize Cell Colors and Font Colors in Excel
In our final step, we will customize the intersecting cell’s interior color and font color as well. For implementation, follow the below steps.
Steps:
- To begin with, insert a module like example 1, and type the following VBA code:
Sub Custom_Inter_Cell()
 Intersect(Range("C4:C9"), Range("B7:C7")).Cells.Interior.Color = rgbBlue
 Intersect(Range("C4:C9"), Range("B7:C7")).Cells.Font.Color = rgbRed
End Sub
- Next, press the Run icon.
- The Intersect() function returns the intersection point of the given ranges.
- The Color and Font.Color applications change the obtained cell’s interior and font color respectively.
- As a result, we change the interior cell color and font color of the intersection cell.
- To ensure that the code runs every time, save the file in the Excel Macro Enable.
- Writing code applicable exclusively to that sheet by writing it in the Sheet rather than the Module. Any other sheet will not function with that code.
Download Practice Workbook
Download this workbook and practice while going through the article.
Conclusion
In conclusion, we have discussed some practical examples of how to use the VBA Intersect method in Excel. Please, leave any further queries or recommendations in the comment box below.
