CODEWITHSUNDEEP
excelvba
Hari Das
Hari Das

Reputation: 10849

How to set background color in a range using VBA?

Here is the code I am trying. But it shows type mismatch error. I don't want to use for loop for setting colors to individual cell.

Function bgColor()
    Dim MyArray(1, 3) As Variant
    MyArray(0, 0) = 37
    MyArray(0, 1) = 12
    MyArray(0, 2) = 15
    MyArray(0, 3) = 18
    Sheets("Data").Range("A1:D1").Interior.ColorIndex = MyArray
End Function

enter image description here

Upvotes: 3

Views: 3682

Answers (1)

dwirony
dwirony

Reputation: 5450

Looping through an array of 5000 colors and writing them to cells took far less than a second, even with screenupdating on. Is there another reason you can't use a For loop?

Sub Test()

    Dim MyArray(0, 4999) As Variant
    Dim i As Long

    For i = 0 To 4999
        MyArray(0, i) = WorksheetFunction.RandBetween(1, 40)
    Next i

    For i = 0 To 4999
        Sheets("Data").Cells(i + 1, 1).Interior.ColorIndex = MyArray(0, i)
    Next i

End Sub

Upvotes: 4

Related Questions