Using VBA to change default color palette

[Garyc]

We currently have an add-in for Excel Xp which changes the default excel
color palette so that it displays our companies corporate color scheme. We
are wanting to use this in Excel 2007 as well but cannot get it to work.
Included is some of the code used for Excel XP

Sub companyPalette()
On Error Resume Next
'Modify the first row to compnay Primary color Palette
ActiveWorkbook.Colors(11) = RGB(160, 0, 80)
ActiveWorkbook.Colors(55) = RGB(225, 110, 0)
ActiveWorkbook.Colors(52) = RGB(235, 175, 0)
ActiveWorkbook.Colors(51) = RGB(180, 190, 0)
ActiveWorkbook.Colors(49) = RGB(0, 180, 175)
ActiveWorkbook.Colors(53) = RGB(230, 80, 50)
ActiveWorkbook.Colors(1) = RGB(0, 0, 0)
ActiveWorkbook.Colors(56) = RGB(153, 153, 153)
ActiveWorkbook.Colors(9) = RGB(255, 255, 255)

Can anyone advise how to amend this code to do the same in excel 2007, i
know Excel 2007 uses a different color palette to excel xp

 

[Peter T]

Your code will work in 2007 and 2007 does support the old 56 palette, albeit
in a limited way.

However 2007 is geared to using Theme colors, you can make your own set and
apply to the workbook. For your purporses probably easier to customize
manually -
Page Layout, Colors, Create New Them colors
Recod a macro to apply the new custom them to a workbook.
You will need to distribute the xml file and save to correct location in
user's systems.

To create a Theme programatically means wrting an xml file to the correct
location (see the recoded macro for the file, open it in say notepad or a
browser). I don't know of anything out there to make it simple, something
I've been meaning to look in to!

If you really want to use the old 56 palette, and I can quite understand why
you might, you'd need to make an app to display the palette and apply the
formats as required. Doable, but a lot of work if you want to cover
everything.

Regards,
Peter T

PS, fwiw and perhaps a bit belated, it cleaner to customize the palette like
this

dim pal
pal = activeworkbook.colors
pal(11) = RGB(160, 0, 80)
pal(55) = RGB(225, 110, 0)
' etc
activeworkbook.colors = pal