To highlight the common cell between 2 ranges using intersect method of application object.
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
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
You can check for more details by opening the link provided in the reference section.
- We can also apply a formula in the intersect cells
Post you may like