Excel VBA Intersect

Below is the syntax of the VBA INTERSECT formula.

  • Arg1 as Range: First intersecting range.Arg2 as Range: Second intersecting range.

In the below examples, we will see some of the useful techniques.

You are free to use this image on you website, templates, etc., Please provide us with an attribution linkHow to Provide Attribution?Article Link to be HyperlinkedFor eg:Source: VBA Intersect (wallstreetmojo.com)

Examples

Example #1

For example, use the below data.

Step 1: Declare the variable as a Variant.

Code:

Sub Intersect_Example()

Dim MyValue As Variant

End Sub

Step 2: Assign the value through the Intersect formula for this variable.

Sub Intersect_Example()

Dim MyValue As Variant

MyValue = Intersect(

End Sub

Step 3: Select the first range as B2 to B9.

Sub Intersect_Example()

Dim MyValue As Variant

MyValue = Intersect(Range(“B2:B9”),

End Sub

Step 4: Select the second range from A5 to D5.

Sub Intersect_Example()

Dim MyValue As Variant

MyValue = Intersect(Range(“B2:B9”),Range(“A5:D5”)

End Sub

Step 5: We are testing with only two ranges here. Close the formula and select the method as a VBA Cell AddressVBA Cell AddressCells are cells of the worksheet, and in VBA, when we refer to cells as a range property, we refer to the same cells. In VBA concepts, cells are also the same, no different from normal excel cells.read more.

Sub Intersect_Example()

Dim MyValue As Variant

MyValue = Intersect(Range(“B2:B9”), Range(“A5:D5”)).Address

End Sub

Step 6: Show the value in the message box in VBAMessage Box In VBAVBA MsgBox function is an output function which displays the generalized message provided by the developer. This statement has no arguments and the personalized messages in this function are written under the double quotes while for the values the variable reference is provided.read more.

Sub Intersect_Example()

Dim MyValue As Variant

MyValue = Intersect(Range(“B2:B9”), Range(“A5:D5”)).Address

MsgBox MyValue

End Sub

We are done and will see what we get in the message box.

We got the result as B5, i.e., the cell address of the intersection point of the supplied range.

Like this using the VBA INTERSECT method, we can do many more things.

Example #2

Select the Intersection Cell

To select the supplied range’s intersection cell, use the code below.

Sub Intersect_Example2()

Intersect(Range(“B2:B9”), Range(“A5:D5”)).Select

End Sub

It will select the intersection cell of the supplied range.

Example #3

Clear Content of the Intersection Cell: To clear the content of the intersection cell of the supplied range, use the code below.

Sub Intersect_Example2()

Intersect(Range(“B2:B9”), Range(“A5:D5”)).ClearContents

End Sub

Example #4

Change the Cell Color Background and Font Color of Intersection Cell: Change the background color of the intersection cell and the font color of the intersection cell value using the code below.

Sub Intersect_Example2()

Intersect(Range(“B2:B9”), Range(“A5:D5”)).Cells.Interior.Color = rgbBlue Intersect(Range(“B2:B9”), Range(“A5:D5”)).Cells.Font.Color = rgbAliceBlue

End Sub

Change the Value of the Intersection Cell: Using the Intersect function, we can also change the value of that cell into something else.

In the above data, the intersection value of the range “B2:B9” and “A5:D5” is cell B5, marked with blue. Now by supplying this range to intersect function, we can change the value to something else.

The below code will change the value from 29398 to “New Value.”

Sub Intersect_Example3()

Intersect(Range(“B2:B9”), Range(“A5:D5”)).Value = “New Value”

End Sub

Run the code above. We will get the word “New Value” in place of 29398.

Like this, by using the Intersect function, we can play around with the middle position value of the supplied range.

Things to Remember

  • In Excel, to get the intersect value of the range, we need to give space characters between two ranges.We can use VBA codingUse VBA CodingVBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task.read more to highlight, format, delete or change, and do many other things to the intersection value.We will get the middle two values if the multiple rows and columnsRows And ColumnsA cell is the intersection of rows and columns. Rows and columns make the software that is called excel. The area of excel worksheet is divided into rows and columns and at any point in time, if we want to refer a particular location of this area, we need to refer a cell.read more supply the Intersect function.

This article has been a guide to VBA Intersect. Here, we learn how to highlight, format, delete or change, and do many other things to the intersection value using the Excel VBA Intersect method with examples and download templates. Below are some useful Excel articles related to VBA: –

  • VBA Comment BlockVBA ChDirSubscript Out of Range in VBAGlobal Variables in VBA