Excel vba changes inner color of cell range without using loops

Question

how to make this work?

Range(Cells(1,1),Cells(height,width)).Interior.Color=colorArray 

colorArray is a one-dimensional array of long integers (width * height) containing color values.

The code above returns a type mismatch error.

 For i = 1 to height For j = 1 to width t=(i-1)*width+j Cells(i,j).Interior.Color=colorArray(t) Next Next 

This code works, but is too slow. I do not want to use loops.

 Range(Cells(1,1),Cells(height,width)).Value=colorArray 

This code fills the range with color values ​​from colorArray without error. I want a similar code to change the background color of cells in this range.

Please, help.

 ReDim colorArray(1 To width*height) As Long 

Siddharth Rout Code Example:

 Sub Sample() Dim colorArray(21) 'or Dim colorArray(21) As Long/Integer Dim Height As Long, Width As Long For i = 0 To 21 colorArray(i) = i Next Height = 10 Width = 2 Range(Cells(1, 1), Cells(Height, Width)).Interior.Color = colorArray End Sub 

@Siddharth Rout, I tested this code, but it also returns the same error "Runtime Error:" 13 "Type Mismatch"

Answer 1

The code above returns a type mismatch error.

John, How did you define colorArray in code? This works for me.

 Sub Sample() Dim colorArray(21) 'or Dim colorArray(21) As Long/Integer Dim Height As Long, Width As Long For i = 0 To 21 colorArray(i) = i Next Height = 10 Width = 2 Range(Cells(1, 1), Cells(Height, Width)).Interior.Color = colorArray End Sub 

Answer 2

I really believe that the problem lies in your call. You should use:

 Range(Cells(1, 1), Cells(Height, Width)).Interior.Color***Index*** = colorArray 

This is my experience using cell background colors in Excel VBA.

-Scott