Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Use VBA Intersect Method in Excel (4 Practical Examples)

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.

excel vba intersect


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.

Display Intersection Point Address Utilizing VBA Intersect Method in Excel

  • Subsequently, the Visual Basic window pops up.
  • Meanwhile, tap Insert and then Module to create a module box.

Display Intersection Point Address Utilizing VBA Intersect Method in Excel

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.

Display Intersection Point Address Utilizing VBA Intersect Method in Excel

  • Thus, we obtain the desired intersection cell address in a message box.

Output of Displaying Intersection Point Address Utilizing VBA Intersect Method in Excel

Code Breakdown:
  • 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.

Highlight Intersection Cell Using VBA Application.Intersect Method in Excel

  • Thus, we get the intersection cell of the ranges selected.

Highlight Intersection Cell Using VBA Application.Intersect Method in Excel Output

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.

Remove Intersection Cell in Excel

  • Hence, we remove the data of the intersection cell C7.

Remove Intersection Cell in Excel

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.

Customize Cell Colors and Font Colors in Excel

  • As a result, we change the interior cell color and font color of the intersection cell.

Customize Cell Colors and Font Colors in Excel

Notes:
  • 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.


Related Articles

Yousuf Khan

Yousuf Khan

Hello! This is MD Yousuf Khan. I am a graduate & post-graduate in Information Technology from Jahangirnagar University, Bangladesh. Currently, I am writing articles for ExcelDemy. I am an independent, self-motivated person with enthusiasm to learn new things, and always try to do my best in any work assigned to me.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo