ColorIndex

[KEVIN BROWN]

What is the difference between the ColorIndex property of the interior object and the color applied to a cell as the result of a conditional format.


--
K.Brown
/*****************************************************************************
To the optimist, the glass is half full.
To the pessimist, the glass is half empty.
To the engineer, the glass is twice as big as it needs to be.
******************************************************************************/

 

[Bob Phillips]

Kevin,

I am not sure I fully understand the question, but here goes.

Each workbook has a colour palette of (I believe) 56 colours. Each colour is given a ColorIndex which can be referred to in code to select a colour from the palette. When you choose a colour from that palette in Excel, it effectively does the same. Any colour can be changed to whatever you want, so for instance the colour red is a ColorIndex of 3 in a standard palette, but index 3 can be changed to any colour, such as green, and loaded into the palette.

It is possible to test the Colorindex of any cell in VBA code. In conditional formatting, you also pick from the colour palette, the same one, and so under the covers it will use the same code as colorindex. The main difference is that you cannot test a colorindex that has been set by CF. In fact, try this little trick. Colour a cell, say to red. If you do a ?Activecell.Interior.ColorIndex in the VBE immediate window, and you will get 3. Put a value in that cell and then add CF, test for that value, and set the pattern for a colour, any colour other than red, say green. So the activecell will now be green due to CF. But if you do an Activecell.Interior.ColorIndex in the VBE immediate window, you will still get a 3. So that cell has 2 colours, although obviously only one is visible.

To test a cell's CF colorindex, you have to test whether it meets the CF criteria, and deduce the colorindex from that.

Does that help?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

What is the difference between the ColorIndex property of the interior object and the color applied to a cell as the result of a conditional format.


--
K.Brown
/*****************************************************************************
To the optimist, the glass is half full.
To the pessimist, the glass is half empty.
To the engineer, the glass is twice as big as it needs to be.
******************************************************************************/

 

[K Bro]

That is exactly my problem when I try to return the colorIndex property
after a cell has been conditionally formated I get a number like -4147.
During my research of this I discovered that number is from the table of
xlConstants. I might try that just for kicks. The color it is set to
after CF is stored somewhere.

Your recomended solution is right on, that would work, but now I have a
quest.

 

[Bob Phillips]

-4147? Do you mean -4142, which is xlColorindexNone, the cell default. All
cells start with this value so you might fail with what you try.

One day I am going to try and write a utility to test a CF colorindex, but
don't hold your breath.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Tom Ogilvy]

Or you could look at Chip Pearson's routine (he has already done it):

http://www.cpearson.com/excel/CFColors.htm
Conditional Formatting Colors