When dealing with Excel, it is important to note that some things are VBA-specific and can only be achieved through VBA.
This refers to functions, formulas, and methods. One of the specific functions is Intersect. In the example below, we will present it.
Use the Intersect Function in VBA
An intersect function is a function that is used to get the range which is a rectangular intersection of several ranges (two or more).
For our example, we will use a table with random numbers from 1,000 to 3,000 in a range A2:D11:
To open the VBA at this point, we need to press ALT + F11 on our keyboard, then choose Insert tab on the window that appears, and then the Module from the dropdown menu:
The window will appear on the right side, and we will insert the following formula into it:
|
1 2 3 |
Sub IntersectExample() Intersect(Range("B4:B10"), Range("A2:C6")).Interior.Color = rgbAquamarine End Sub |
This code will basically find the cells that are a part of the first range (B4:B10) and the second range (A2:C6) and will paint those cells in aquamarine blue color. Once we execute it by pressing F5 on our keyboard, this will be our result:
From the table above, it is clear that cells B4, B5, and B6 are the ones that can be found in both ranges that we defined.
There is one thing that we need to have in mind, though. There is a possibility that there are no cells that intersect in desired ranges. For these cases, we need to prepare the IF function, to make sure we cover this scenario.
In these situations, we can use the formula like this one:
|
1 2 3 4 5 6 7 8 9 |
Sub IntersectExample2() Dim i As Range Set i = Application.Intersect(Range("B4:B10"), Range("A2:C6"), Range("D1:D4")) If i Is Nothing Then MsgBox "No intersection" Else i.Interior.Color = rgbBeige End If End Sub |
In the formula above, we first declare the variable “i” as a range. Then we set this variable to be equal to the intersection of ranges B4:B10, A2:C6, and D1:D4. It is painfully evident that these three ranges do not have a cell or cells that can be attributed to all ranges.
Due to that, we create the If Else function that defines the message “No intersection” if these cells do not exist. If they do, these cells will be colored beige.
This is what our formulas look like in the module:
When we execute the second formula, we will end up with our defined message on the worksheet:
Which will lead us to change the formula, or define a different range.