Excel 2010 Macro Create a New Range from Overlapping Ranges
The Intersect method returns the cells that overlap between two or more ranges:
Application.Intersect(argument1, argument2, etc.)
The following code colors the overlapping cells of the two ranges:
Set IntersectRange = Intersect(Range(“Range1”), Range(“Range2”))
IntersectRange.Interior.ColorIndex = 6