Use intersect method in VBA Excel -debugvba.com

Intersect method vba

Objective

To highlight the common cell between 2 ranges using intersect method of application object.

Approach

Here we have used the intersect method which returns the range which overlap between 2 or more ranges. For example, there are 2 ranges A1:A10 and A10:C10, then the cells which is common between these 2 ranges is cell A10. So, we can highlight the common cell using intersect method, we could have used more than 2 ranges as well.

We have also added a string value i.e. “Test” in the intersect cell.

Sub IntersectRange_1()

'Select the intersect of 2 ranges

Intersect(Range("A1:A10"), Range("A10:C10")).Select

'Highlight the colour of common cell

Selection.Interior.Color = RGB(255, 255, 10)

Selection.Value = "Test"

End Sub

 

Intersect method vba

 

In the second case we had 2 ranges, first one was C4:I4, while second one was G4:L4. Both the ranges overlap each other horizontally. Cells G4, H4, I4 are the common cells. In the image shown below we can see that we have highlighted the common cells.

 

Sub IntersectRange_2()

'Select the intersect of 2 ranges

Intersect(Range("C4:I4"), Range("G4:L4")).Select

'Highlight the colour of common cell

Selection.Interior.Color = RGB(255, 255, 10)

End Sub

 

Intersect method vba

You can check for more details by opening the link provided in the reference section.

Notes

  1. We can also apply a formula in the intersect cells

Reference

https://docs.microsoft.com/en-us/office/vba/api/excel.application.intersect

Post you may like

How to select merged cell in VBA Excel