Setting Colors in Excel 2010

[Rubberduckone]

Hi,

Two color questions

Question 1
I have an old app in which I was setting the color palette with its 56 different colors. Once converting to O2010, I thought I could convert it easily. However with the concepts of themes and 6 accent color bases, this has got me a bit confused.

I do understand that I can create "sets" of colors and save them as themes, no problem.

In the old palette each of the 40 different "colorindex" numbers could have a unique color very different to the others. In O2010 I can only see 6 different "accents" within a theme, that can then provide me with numerous variations of the 6 colors. But, if I have say 9 very distinct corporate colors, how do I then get those into a single theme?

My current understanding is that I can only have 6 "Base" colors - is this correct?

Question 2
Is there by means of VBA code a way to set the colors and to create a theme? (I've found the method for loading the theme, but are missing the others)
- or would you happen to have a small piece of sample code that contains the basics for setting the colors and creating a theme

Bests
RD

[HansV]

Color themes look like an attempt by Microsoft to make working with colors hopelessly complicated. Applying exactly the same theme color in Word and in Excel has different results, and applying exactly the same theme color to a cell and to a shape in Excel has different results too (see Exploring Theme Colors).

Support for color themes in VBA is half-baked. Not all aspects are exposed in the object model. In particular, it doesn't seem possible to create or modify a theme directly. You have to modify the relevant XML files for that.

You can still set the ColorIndex of the interior and font of a cell, and this will apply the colors from the 56-color palette. And you can set the interior and font of a cell to any RGB color

[Rubberduckone]

YAK - was afraid you would answer something like this

So themes are basically useless as they only can hold 6 different colors + 2 backgrounds and cannot be controlled through VBA - unless they are "XML-modified".

If I read your suggestion correct you recommend to do the VBA modification directly to the individual color by means of .colorindex or .rgb ??

PS: The theme stuff looks to me to be an attempt to imitate functionality from Adobe Illustrator


Cheers
RD

[HansV]

A theme consists of 16 colors, of which some are paired, so only 12 are shown when editing a theme:

x832.png

But 2 of those are reserved for hyperlinks, and 2 others aren't directly available in color palettes.

If do indeed find it easier to set the ColorIndex (1-56) or Color (RGB value) directly instead of applying theme colors.