apply the cell value as the fill colour

[BFrassek]

I have the following question:

Is there a way in VBA for a bigger table (400x400 cells) to apply the value of each cell as the interior colour "at once"?
"At once" means, that I do not need loops to fill the cells which will take a few seconds.

I've tried out something like

Range("A1:OJ400").Interior.Color = Range ("A1:OJ400").value

but will get a "types incompatible" message.

Best regards
Bernd Frassek

 

[jeffreybrown]

Something like this maybe which will turn the interior color Blue

Sub InteriorColor()
    Range("A1:OJ400").Interior.ColorIndex = 5
End Sub

Color Palette and the 56 Excel ColorIndex Colors

 

[Rick Rothstein]

I have the following question:

Is there a way in VBA for a bigger table (400x400 cells) to apply the value of each cell as the interior colour "at once"?
"At once" means, that I do not need loops to fill the cells which will take a few seconds.

Are you filling each cell with the same color... or does each cell get colored differently?

Show us a sample of the values you have in the cells.

 

[BFrassek]

Are you filling each cell with the same color... or does each cell get colored differently?

Show us a sample of the values you have in the cells.

Each cell of the table (A1:OJ400) contains a RGB-value (Maximum value 256^3-1).
The purpose is to set the cell Background Color according to the cell's value.
A solution with Loops does this:

For y = 400 To 1 Step -1
For x = 1 To 400 Step 1
Cells(y, x).Interior.color = cells(y,x).value
Next x
Next y

But it is slow; therefore, I asked the question if there is any "at once" solution ...

Best regards
Bernd Frassek (Germany)

 

[mikerickson]

You do need a loop, but you can do a faster loop than that.
Reading a block of cells is much faster that a loop reading each one.

Dim myColors as Variant
Dim I as Long, J As Long

With Range("A1:OJ400")

    myColors = .Value

    For I = 1 to .Rows.Count
        For J = 1 to .Columns.Count
            .Cells(I, j).Interior.Color = myColors(I,j)
        Next j
    Next I

End With