CODEWITHSUNDEEP
excelvba
Error 1004
Error 1004

Reputation: 8220

Apply formatting to a range using array loop - VBA

Using VBA i make to:

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.

Upvotes: 2

Views: 227

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57683

  1. 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

  2. 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

  3. 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

  4. 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.

Upvotes: 3

Related Questions