Apply formatting to a range using array loop - VBA

Question

Using VBA i make to:

Set two ranges
Create an array of all ranges names
Loop the array in order to apply formatting to each range in the array

BUT on line .Interior.Color = vbRed

i receive the below error:

Run-time error '424': Object required

Code

Sub test()

    Dim rng1 As Range, rng2 As Range
    Dim strRanges As Variant
    Dim i As Long

    Set rng1 = Sheet1.Range("A1:D1")
    Set rng2 = Sheet2.Range("C5:H5")

    strRanges = Split("rng1,rng2", ",")

    For i = LBound(strRanges) To UBound(strRanges)
        With strRanges(i)
            .Interior.Color = vbRed
        End With
    Next i

End Sub

i have already use:

With Range(strRanges(i))

instead of:

With strRanges(i)

without any luck!

Any help will appreciate.

Change strRanges = Split("rng1,rng2", ",") in strRanges = Split(rng1.Address & "," & rng2.Address, ",") and use With Range(strRanges(i)). You cannot build an object range from a string (directly)... — FaneDuru
– FaneDuru, Commented Feb 5, 2020 at 12:03
Agree with @FaneDuru with one notice - as far as you are using ranges on different sheets it would work properly with strRanges = Split(rng1.Address(External:=True) & "," & rng2.Address(External:=True), ",") line instead. Otherwise, you will paint ranges on active sheet only. — Vitaliy Prushak
– Vitaliy Prushak, Commented Feb 5, 2020 at 12:20
Correct. Or use a workaround in the loop: if i = 0 then ... With Sheet1.Range(strRanges(i)) ... Else ... Sheet2.Range(strRanges(i))... But your solution is obviously more elegant. — FaneDuru
– FaneDuru, Commented Feb 5, 2020 at 12:30
I am afraid that Union is not possible for ranges in different sheets... — FaneDuru
– FaneDuru, Commented Feb 5, 2020 at 13:47

Pᴇʜ · Accepted Answer · 2020-02-05 14:54:33Z

3

You can do this in just one line if the ranges are in the same worksheet
```
Sheet1.Range("A1:D1,C5:H5").Interior.Color = vbRed
```

You can use union if the ranges are in the same worksheet

Dim rng1 As Range, rng2 As Range
Set rng1 = Sheet1.Range("A1:D1")
Set rng2 = Sheet1.Range("C5:H5")
Union(rng1, rng2).Interior.Color = vbRed

You can use real arrays for your ranges if they are in different worksheets

Sub test()    
    Dim rng(1 To 2) As Range
    Set rng(1) = Sheet1.Range("A1:D1")
    Set rng(2) = Sheet2.Range("C5:H5")

    Dim i As Long
    For i = LBound(rng) To UBound(rng)
        With rng(i)
            .Interior.Color = vbRed
        End With
    Next i    
End Sub

If you don't have numbered range variable names then you can use another array:

Sub test()    
    Dim rngABC As Range, rngXYZ As Range
    Set rngABC = Sheet1.Range("A1:D1")
    Set rngXYZ = Sheet1.Range("C5:H5")

    Dim ArrRng() As Variant
    ArrRng = Array(rngABC, rngXYZ)

    Dim rng As Variant
    For Each rng In ArrRng
        rng.Interior.Color = vbRed
    Next rng     
End Sub

Note that if you think you have to use numbered variable names like

Dim rng1 As Range, rng2 As Range

this always is a clear sign for using an array instead:

Dim rng(1 To 2) As Range

numbered variable names are a bad practice. Always choose meaningful names.

edited Feb 5, 2020 at 14:54

answered Feb 5, 2020 at 13:44

Pᴇʜ

57.9k10 gold badges55 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

BigBen Over a year ago

My guess is that OP is working with a dynamically created String of all the ranges in question... which is a bad idea in the first place... but could be wrong. In any case this is the right approach.

FaneDuru Over a year ago

@BigBen: Ups... Again did not read with enough attention til the end... I will delete mai comment.

FaneDuru Over a year ago

@BigBen: Or, maybe I did not refresh my page and that's why i did not see something maybe not existing... (Good excuse :))...

Error 1004 Over a year ago

@BigBen i have create an array with all my ranges and loop the ranges in the array in order to apply some formatting. Thanks for you answer. it s works. Could you please share it as an answer in order to vote?

BigBen Over a year ago

@Error1004 - PEH's answer works as it uses an array.

|

Collectives™ on Stack Overflow

Apply formatting to a range using array loop - VBA

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related