EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home VBA VBA Resources VBA Math & Trig Functions VBA Intersect
Secondary Sidebar
VBA Math & Trig Functions
  • VBA Math & Trig
    • VBA Random Number
    • VBA Number Format
    • VBA Integer
    • VBA MOD
    • VBA IsNumeric
    • VBA Round
    • VBA INT
    • VBA RoundUp
    • VBA Intersect
    • VBA Randomize
    • VBA Square Root

VBA Intersect

By Ashwani JaiswalAshwani Jaiswal

VBA Intersect

Excel VBA Intersect

VBA Intersect in mathematics or in geometry means when two or more lines or area crosses each other. The common point or area created after that is called Intersection point or area. In excel also we can highlight and measure the Intersect area.

Syntax of Intersect Function in Excel VBA

Intersect function has the following syntax in Excel VBA :

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

All in One Financial Analyst Bundle(250+ Courses, 40+ Projects)
Financial ModelingInvestment BankingUS GAAPCFA-Level 1 & 2
Equity ResearchM & A ModelingPrivate Equity ModelingForex Trading
Price
View Courses
250+ Online Courses | 40+ Projects | 1000+ Hours | Verifiable Certificates | Lifetime Access
4.9 (86,704 ratings)

Syntax of VBA Intersect

As we can see, Arg1 and Arg2 are mentioned, Range. And rest of the arguments are in brackets. Which means that the first two arguments must be selected as Range. Or in other words, minimum 2 areas must be included for finding Intersect. Rest of the arguments can be selected as Range or it can include some other things or parameters as well as per need. This syntax can accommodate a maximum of 30 Arguments.

How to Use Excel VBA Intersect Function?

We will learn how to use a VBA Intersect function with few examples in Excel.

You can download this VBA Intersect Excel Template here – VBA Intersect Excel Template

VBA Intersect – Example #1

In the first example, we will highlight and create Intersection area when we have some dataset. For this, we have sample data which has 3 columns filled with numbers as shown below.

VBA Intersect Example 1-1

Now we need to find the area of intersection of an above data table using VBA Intersect. For this, follow the below steps:

Step 1: Go to the VBA window and open a Module from the Insert menu option as shown below.

VBA Intersect Example 1-2

We will get a blank window of the module.

Step 2: Now write Subcategory of VBA Intersect or in any other name as per your choice.

Code:

Sub VBAIntersect1()

End Sub

VBA Intersect Example 1-3

Step 3: Now directly insert Intersect command as shown below.

Code:

Sub VBAIntersect1()

Intersect(

End Sub

VBA Intersect Example 1-4

As we already explained the detailed syntax of Intersect, we will add an area of intersection. We can choose N number of ranges but a minimum of two Ranges should be there.

Let’s consider below an area of intersection where the first area is from A1 to B8, the second area is B3 to C12 and the third area is A7 to C10. We can consider and choose any combination of a pattern of intersections.

VBA Intersect Example 1-4A

Now let’s see at what point (/s) these areas meet and intersect each other. The common area created by all the above areas will be our area of intersection.

Step 4: Now in VBA Module of Intersect, select the first area range as shown below.

Code:

Sub VBAIntersect1()

Intersect(Range("A1:B8")

End Sub

VBA Intersect Example 1-5

We have added the first range, but our syntax is still incomplete.

Step 5: Now further insert rest of two areas which we have discussed above separated by commas.

Code:

Sub VBAIntersect1()

Intersect(Range("A1:B8"), Range("B3:C12"), Range("A7:C10"))

End Sub

VBA Intersect Example 1-6

Step 6: Now give the condition as “True”.

Code:

Sub VBAIntersect1()

Intersect(Range("A1:B8"), Range("B3:C12"), Range("A7:C10")) = True

End Sub

VBA Intersect Example 1-7

This completes our code.

Step 7: Now compile the code and run by clicking on the Play button which is located below the menu bar as shown below.

VBA Intersect Example 1-8

We will get the common area or intersected area which has value TRUE as shown above. Although we got the intersect area, that TRUE has replaced the data which was there in the intersected area.

Step 8: Now to avoid losing this we can change the background of color, those common cells to any color of our choice. For this after the syntax of Intersect use Interior function along with Color as shown below.

Code:

Sub VBAIntersect1()

Intersect(Range("A1:B8"), Range("B3:C12"), Range("A7:C10")).Interior.Color =

End Sub

Interior function Example 1-9

Step 9: Now in VBA, we cannot use the name of the color which we want to use directly. For this, we need to add “vb” which is used to activate the colors available in VBA. Now use it and add any name of the color of your choice. We are selecting Green here as shown below.

Code:

Sub VBAIntersect1()

Intersect(Range("A1:B8"), Range("B3:C12"), Range("A7:C10")).Interior.Color = vbGreen

End Sub

Interior function green color Example 1-10

Step 10: Now again compile the written code in one go as the code is quite small and run it.

VBA Intersect Example 1-11

We will see the color of the intersected area is changed to Green and common area which is created by the intersection of different 3 areas in B7 to B8.

VBA Intersect – Example #2

There is another but a quite a different way to use VBA Intersect. This time we use intersect in a specific worksheet. In Sheet2 we have marked an area from B4 to E8 as shown below.

VBA Intersect Example 2-1

Follow the below steps:

Step 1: In VBA, go to Sheet2 of current Workbook as shown below.

VBA Intersect Example 2-1.1

Step 2: Now select the Worksheet from this first drop down option. This will allow the code to be used in this current sheet only.

Select Worksheet Example 2-2

Step 3: And from the second drop-down select the option Change as shown below. This is used to target the changes done in the selected range.

Change Option Example 2-3

Step 4: We will write our code in the first Subcategory only.

Code:

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

VBA Intersect Example 2-4

Step 5: We will use the If-Else loop for forming a condition for intersect function.

Code:

Private Sub Worksheet_Change(ByVal Target As Range)

If

End If

End Sub

If-Else loop Example 2-5

Step 6: First select the target range from B4 to E8 as shown below. This will target intersect of the area covered in B4 to E8 mainly.

Code:

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("B4:E8"))

End If

End Sub

VBA Intersect Example 2-6

Step 7: And if there is nothing in the target area then we need to write a statement which will redirect the code ahead.

Code:

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("B4:E8")) Is Nothing Then

End If

End Sub

VBA Intersect Example 2-7

Step 8: And if really the target is out of range, we can use a message box with a message of alert as shown below.

Code:

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("B4:E8")) Is Nothing Then

  MsgBox "Out of Range"

End If

End Sub

VBA Intersect Example 2-8

Step 9: And In the Else statement where something in written inside the box then we should get a prompt message if the written content is inside the box as shown below.

Code:

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("B4:E8")) Is Nothing Then

  MsgBox "Out of Range"

Else

  MsgBox "Within Range"

End If

End Sub

VBA Intersect Example 2-9

Step 10: Now compile each step of written code and close the worksheet of VBA. As we have written the code specific to the sheet then it will work in the same.

Step 11: Now write something inside the box.

Within Range Example 2-10

As we can see we wrote 2 in cell C5 inside the box we got the message or “Within Range”.

Step 12: Again write something out of the box. We wrote 1 in cell B10 and we got the message of “Out of Range” as shown below.

Out of Range Example 2-11

This is another way to using Intersect in Excel VBA.

Pros of Excel VBA Intersect

  • It is very easy to at least highlight the area which intersects by the process of example-1.
  • This is very useful where we need to filter or work on that kind of data which has intersection from a different area such as Dates, Owner, etc.

Things to Remember

  • Remember to save the file in Macro Enable Excel format, so that code will function in every use.
  • Writing code in Sheet instead of the module as shown in example-2, make the code applicable only for that sheet. That code will not work on any other sheet.
  • Using Target Range as shown in example-2 is useful in specifying the area to hit.

Recommended Articles

This is a guide to VBA Intersect. Here we discuss how to use Excel VBA Intersect Function along with some practical examples and downloadable excel template. You can also go through our other suggested articles –

  1. VBA Loops
  2. VBA Web Scraping
  3. VBA Do Until Loop
  4. VBA CDEC
Popular Course in this category
VBA Training (4 Courses, 12+ Projects)
  4 Online Courses |  13 Hands-on Projects |  50+ Hours |  Verifiable Certificate of Completion
4.7
Price

View Course
2 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign In
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more