ListObjects and individual cell processing

[rdilipk]

This is a bit of an open-ended question.

I am using ListObjects quite a bit to render table-like data onto Excel using VBA. I basically pass a two dimensional array of type 'System.Object' from managed code (C#) and through automation, accept it as Variant on the VBA side through a macro.

Even if I pass a very big matrix, say 10,000+ rows, it gets rendered fairly quickly when I simply assign the 2d matrix, to say, a Range.

However, if I have to modify the attributes (like color for example) of data in individual cells, I can only seem to do it this way:

Cells(1, 1).Interior.Color = 33

that is, cell-by-cell, which is way too slow when I have to deal with tens and thousands of rows.

Just like one is able to block copy a 2d array into Range.Value and let Excel worry about how to display it properly as rows and columns, is there a way to maintain a 2d array of colors and expect Excel to do the looping internally for me once I give it a range where these colors must be applied? is there such a property on Range?

 

[RoryA]

Not really. Why do you need to colour individual cells? If it's simply to flag their values you should probably look at adding conditional formatting to the table.

 

[rdilipk]

Can you elaborate? How do I go about conditionally formatting a table?

BTW, this post:
Excel vba change interior color of range of cells without using loops - Stack Overflow

seems to indicate its possible to block update color but as the OP indicates, I get a Type Mismatch error too. Is there something wrong with that example?

 

[RoryA]

I think Sid's solution is incorrect.

On what basis do you colour the cells?

 

[rdilipk]

I think Sid's solution is incorrect.

On what basis do you colour the cells?

I probably have to invent such a basis myself but for arguments sake, consider:

dim values(1, 1) as variant
dim colors(1, 1) as variant

values(0, 0) = 1
values(0, 1) = 2
values(1, 0) = 3
values(1, 1) = 4

colors(0, 0) = 3
colors(0, 1) = 4
colors(1, 0) = 5
colors(1, 1) = 6

The idea is every element in the 'values' 2d array is matched with another 2d array (colors) that determines what their colors should be.

So if I am able to do:

Range.Value = values

I was hoping to do:

Range.Interior.ColorIndex = colors

so the colors neatly line up with their elements.

I can't seem to find a straight forward way to do this.

 

[RoryA]

Like I said, you can't do that. You could either add multiple conditional formatting rules to colour the cells based on their values, or you'd have to union ranges for all the cells of a certain value so that you can colour all the cells in one go rather than doing them one by one.