Referring to ranges
- By Bill Jelen and Tracy Syrstad
- 3/11/2022
Contents
×
- The Range object
- Referencing ranges in other sheets
- Referencing a range relative to another range
- Using the Cells property to select a range
- Using the Offset property to refer to a range
- Using the Resize property to change the size of a range
- Using the Columns and Rows properties to specify a range
- Using the Union method to join multiple ranges
- Using the Intersect method to create a new range from overlapping ranges
- Using the IsEmpty function to check whether a cell is empty
- Using the CurrentRegion property to select a data range
- Using the Areas collection to return a noncontiguous range
- Referencing tables
- Next steps
Using the Intersect method to create a new range from overlapping ranges
The Intersect method returns the cells that overlap between two or more ranges. If there is no overlap, an error is returned:
Application.Intersect(argument1, argument2, etc.)
The expression Application is not required. The following code colors the overlapping cells of the two ranges:
Set IntersectRange = Intersect(Range("Range1"), Range("Range2"))
IntersectRange.Interior.ColorIndex = 6