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?
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,503
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
35,503
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
35,503
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,969
Messages
5,525,958
Members
409,673
Latest member
Riseee

This Week's Hot Topics

Top