Yousuf Khan
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 to use VBA Intersect method in Excel.
Download Practice Workbook
Download this workbook and practice while going through the article.
4 Practical Examples to Use VBA Intersect Method in Excel
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 address, 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.
Read More: Intersection of Two Columns in Excel (7 Easy Methods)
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 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.
Read More: Intersection of Row and Column in Excel is Called a Cell
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.
Read More: How to Find Intersection of Two Lists in Excel (3 Easy Methods)
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.
Read More: Performing Intersection of Two Data Sets in Excel (4 Easy Ways)
Conclusion
In conclusion, we have discussed some practical examples of how to use the VBA Intersect method in Excel. Not to mention, our ExcelDemy website shows various simple problem-solving methods like this. Please, leave any further queries or recommendations in the comment box below.
