How can I load color index of each cell in a range into an array?

Question

I am trying to dump range(A1:A30)into an array. It works fine when I am extracting the cell value for the range.

 aM = Sheet2.Range("A1:A30").Value

But, I require the Color Index of each cell in that range.I know, I can get this done by looping through each cell in that range. For efficiency purpose I don't want to use the looping option.

Can I achieve this by using the following code/alternative method?

Dim aM() As Variant
aM = Sheet2.Range("A1:A30").Interior.ColorIndex
For i = LBound(aM) To UBound(aM)
    Debug.Print aM(i, 1)
Next i 

Answer 1

Can I achieve this by using the following code/alternative method?

You have to loop. Even if it was possible to do it like this am = Range.Interior.ColorIndex there would be a hidden loop implemented behind it. In aM = Sheet2.Range("A1:A30").Value there is also a loop implemented, you just do not see it.

In general, loading 300K colors takes 2 seconds with me:

Public Sub TestMe()

    Dim i               As Long
    Dim aM(300000)      As Long

    For i = LBound(aM) To UBound(aM)
        aM(i) = Cells(i + 1, 1).Interior.ColorIndex
    Next i

End Sub