ListObjects and individual cell processing

rdilipk

New Member
Joined
Nov 17, 2011
Messages
36
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?
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
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.
 
Upvote 0
I think Sid's solution is incorrect.

On what basis do you colour the cells?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,194
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top