CODEWITHSUNDEEP
excelvbarange
Sunni
Sunni

Reputation: 365

VBA Range issued

Sub FormatRange()

Dim xName As Range

Set xName = Range("B3", Range("B2").End(xlDown))

Sheet2.Activate

xName.Font.Color = vbRed

xName.Font.Italic = True

End Sub

in the above code I set my initial range in Sheet1. without a referece to sheet1. Even after I activated sheet2. The formatting get applied to sheet1 range. how come this is possible? why it didn't get applied to sheet2?

Upvotes: 1

Views: 62

Answers (2)

Dominique
Dominique

Reputation: 17491

Does this solve your issue? (not tested)

Set xName = ActiveSheet.Range("B3", ActiveSheet.Range("B2").End(xlDown))

Upvotes: 0

Siddharth Rout
Siddharth Rout

Reputation: 149305

When you define a range and assign it to a range object then it refers to the sheet which was active at that time. That is the beauty of it. This way you do not need to use .Select/.Activate to work with the range object.

The thing is that you are only looking at the Rng.Address. To see the complete address try this

Sub Sample()
    Dim rng As Range

    Set rng = Range("A1")

    Debug.Print rng.Address(, , , True)
End Sub

You may want to read up on Range.Address property (Excel)

PS: As mentioned in the comment, .Parent.Name would have given you the clue. It shows you the "Parent" sheet of that range :)

Upvotes: 1

Related Questions