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

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
38,782
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
38,782
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
I think Sid's solution is incorrect.

On what basis do you colour the cells?
 

rdilipk

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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
38,782
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,015
Messages
5,834,939
Members
430,327
Latest member
Mr_Himalayan778

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
Top