Till Excel 2003, the below command was used for retrieving the
available colors to be used,

activeworkbook.colors(1...56)

Returns or sets colors in the palette for the workbook. The palette
has 56 entries,


Now with Excel 2007+2010, the same command can be used but it does not
return the theme colors.

Which command can I used in order to retrieve the colors given in HOME
ribbon, FILL COLOR (background) option which includes 70 colors (60
theme colors + 10 standard colors)?

Thanks a lot in advance!

You can get the top row easily enough with
wb.Theme.ThemeColorScheme.colors(index)

note the order of the first four is not quite as expected, dark & light are
reversed in the first two pairs.

To get the tints & shades is not straightforward. Depending on the
themecolor there are five different algorithms that set the tints based on
the themecolor. Doable but a lot of work.

FWIW I have an addin that's "work in progress" (iow I look at it every few
months on a rainy day), which does all that. Also gets all colours (RGBs) of
all ColorSchemes from file (builtin & custom), including tints and shades
(to within 1 or 2 units of RGB). Also, can customize an existing colorscheme
or make a new one in "real time" (eg copy a pixel to directly into the
palette. Also shows old & new palettes together and can use either, also can
customize an "the old 56 colour palette" with the current theme palette.
Also, quite a lot more. Not sure if it includes anything that might fulfil
your overall objective though, maybe - can dump entire palette or individual
colour details to cells.

Regards,
Peter T

"chrisdev" wrote in message
...
Till Excel 2003, the below command was used for retrieving the
available colors to be used,

activeworkbook.colors(1...56)

Returns or sets colors in the palette for the workbook. The palette
has 56 entries,


Now with Excel 2007+2010, the same command can be used but it does not
return the theme colors.

Which command can I used in order to retrieve the colors given in HOME
ribbon, FILL COLOR (background) option which includes 70 colors (60
theme colors + 10 standard colors)?

Thanks a lot in advance!

On Sep 16, 4:19*am, chrisdev wrote:
Till Excel 2003, the below command was used for retrieving the
available colors to be used,

activeworkbook.colors(1...56)

Returns or sets colors in the palette for the workbook. The palette
has 56 entries,

Now with Excel 2007+2010, the same command can be used but it does not
return the theme colors.

Which command can I used in order to retrieve the colors given in HOME
ribbon, FILL COLOR (background) option which includes 70 colors (60
theme colors + 10 standard colors)?

Thanks a lot in advance!

Can you try activeworkbook.colorindex( )