Intersection

Basic Exercise

Statement

Determine the resulting area from the "Ranges" intersection and change the background color to green.

	Initial cell	Offset	Resize
Range "V"	"B3"	(4,1)	(11,5)
Range "B"	"H3"	(-6,-3)	(8,4)
Range "A"	"A21"	(-9,5)	(9,4)

Suggested Concepts:

Dim - Declares a variable.
Set - Assigns an object reference to a variable or property.
Names - Represents the name of an object. May be used to name cells in a worksheet.
Resize - Resize the selection.

Intersect - Returns a range that represents the intersection of two or more ranges.

Intersect(Range("A1:A2"), Range("A2:B2")).Value = 0
'Only the result of the intersection ("A2") is assigned with the value 0

Remember that in most cases there is more than one way to solve the exercise.

Hint: Note that the Resize property accepts anly one positive argument.

Solved Exercise

Exercise Solution

Name the ranges as defined in the exercise statement

Names.Add Name:="V", RefersTo:=Range("B3").Offset(4, 1).Resize(11, 5)
Names.Add Name:="B", RefersTo:=Range("H3").Offset(3, -6).Resize(8, 4)
Names.Add Name:="A", RefersTo:=Range("A21").Offset(-8, 4).Resize(9, 4)

Declere the variable. Use the Set statement to create an object that is the intersection of the ranges "V", "B" and "A"
```
Dim Rng As Range        
Set Rng = Intersect(Range("V"), Range("B"), Range("A"))
```
Change the background color defined by the variable Rng
```
Rng.Interior.Color = RGB(140, 198, 63)
```

Consolidated Answer

    Sub Solution()

    Dim Rng As Range

    Names.Add Name:="V", RefersTo:=Range("B3").Offset(4, 1).Resize(11, 5)
    Names.Add Name:="B", RefersTo:=Range("H3").Offset(3, -6).Resize(8, 4)
    Names.Add Name:="A", RefersTo:=Range("A21").Offset(-8, 4).Resize(9, 4)

    Set Rng = Intersect(Range("V"), Range("B"), Range("A"))
    Rng.Interior.Color = RGB(140, 198, 63)

    End Sub

Show Answer

Download exercise Download answer

SuperExcelVBA.com is learning website. Examples might be simplified to improve reading and basic understanding. Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. All Rights Reserved.

Excel ® is a registered trademark of the Microsoft Corporation.

INTRODUCTION

BASIC

INTERMEDIATE

ADVANCED