How to refer to two color palletes at same time in Excel VBA chart

Question

I have done VBA for making line chart with chart having more than 20 series. I want to give colors to the lines using msoThemeColorAccent1, msoThemeColorAccent2, msoThemeColorAccent3 etc.. all the way to msoThemeColorAccent6. But using numbers 1 to 6, I am able to give only six colors of chosen color palette, but I also want to use colors of other palette without manually choosing them.

If I chose 2nd palette manually, the numbers 1 to 6 start referring to other palette.

I think there is some concept of ThemeColorScheme but not sure how to use the same.

Any guidance would be helpful.

Thanks

Answer 1

Note: The following is True for all kind of Excel objects, like cells, shapes, chart-series...

When you use a palette, the idea is to store a kind of index into the palette. There are basically 10 colors in a palette (there are some more, but let's keep it simple), those are the colors (from left to right) that you can see in the color picker. The index values are 1-10, and you can access them with

xlThemeColorDark1        1
xlThemeColorLight1       2
xlThemeColorDark2        3
xlThemeColorLight2       4
xlThemeColorAccent1-6    5-10

When you look at the color picker, you see that there are many lighter and darker variations of a color. When you select a color, two properties are written: ThemeColor and TintAndShade. The latter defines how dark or light a color is used.

Now (as you already wrote), when you change the colors of your document by choosing a different Theme (or only the colors of the Theme), the objects of your Excel will change to the new palette. This is because you said (for example) use xlThemeColorAccent1 (the 5th color of your palette), and now the 5th color is a new, different color. As long as you use ThemeColor, there is no way around that.

But there is an alternative: When you open the color picker in Excel and choose "More Colors", you can pick any color you want. This is done by writing the Color-property. The Color-property saves the RGB-Value of a color (as a Long Integer). When you use the Color-property, Excel will "forget" the ThemeColor-Index (set it to 0). Once you define a color that way, changing the palette will not change the color of the object (because the ThemeColor-Index is 0 and the color is read from the Color-property).

So what you have to do is to define the Colors you need and figure out the RGB-value. The following small routine can help you. It will write the color value of a cell into that cell.

Open a blank sheet and set the color for as many cells you want (using the Theme colors or individual colors). Select the colored cells and call the macro.

Sub WriteAndkeepColors()
     Dim cell As Range
     For Each cell In Selection
        cell.Value = cell.Interior.Color
        cell.Interior.Color = cell.Interior.Color ' Removes ThemeColor
    Next
End Sub

The handy thing is that if you format a cell using ThemeColor, Excel automatically sets the Color-property, so with this method you get the color values of all cells, no matter how they where colored. By writing the Color-value to itself, the ThemeColor-index is "forgotten". You can now even change the Theme, all colors remain (so you can format some more cells, rerun the macro and get the colors of the new Theme).

Now you need to copy the color values into your code and use them:

Sub colorChartLines(co As ChartObject)
    Dim MyColors(1 To 20) As Long
    
    MyColors(1) = 4697456   ' Green
    MyColors(2) = 12874308  ' Blue
    MyColors(3) = 49407     ' Orange
    MyColors(4) = RGB(127, 127, 127)  ' Define by using RGB-function
    MyColors(5) = vbRed     ' Use a predefined VBA color
    (...)
    MyColors(20) = 16738047 ' Pink

    Dim ser As Series, sI As Long
    For sI = 1 To co.Chart.FullSeriesCollection.Count
        Set ser = co.Chart.FullSeriesCollection(sI)
        Dim colorIndex As Long
        colorIndex = (sI - 1) Mod UBound(MyColors) + 1
        Debug.Print sI, colorIndex
        ser.Format.Line.ForeColor.RGB = MyColors(colorIndex)
    Next
End Sub

(Note that for Series, the property name for Color is RGB and for ThemeColor it is ObjectThemeColor)

Answer 2

Excel uses the six accent colors of one theme to get more colors by changing brightness. For series 1-6, it uses the theme colors 1-6 with brightness 0. For series 1-6, it uses theme colors 1-6 with brightness -0.4. Then it uses brightnesses of 0.2, -0.2, 0.4, -0.5, 0.3, -0.3, 0.5, and then back to 0. (Yes, I made a chart with a zillion series so I could figure this out, why wouldn't I?).

You could use the above approach. Or you could use something like the following.

Workbook starts with theme 1. Apply theme colors 1-6 to the first 6 series. It uses code like this (I'm paraphrasing: you need to write actual code)

With series1-6.Format
  ' fill format as needed
  .Fill.Forecolor.ObjectThemeColor = msoThemeColorAccent1-6
  ' convert theme info to straight RGB
  .Fill.Forecolor.RGB = .Fill.Forecolor.RGB
  ' line format as needed
  .Line.Forecolor.ObjectThemeColor = msoThemeColorAccent1-6
  ' convert theme info to straight RGB
  .Line.Forecolor.RGB = .Line.Forecolor.RGB
End With

Now switch the workbook to another theme. The colors above shouldn't change, since you set their RGB values. And do this:

With series7-12.Format
  ' fill format as needed
  .Fill.Forecolor.ObjectThemeColor = msoThemeColorAccent1-6
  ' convert theme info to straight RGB
  .Fill.Forecolor.RGB = .Fill.Forecolor.RGB
  ' line format as needed
  .Line.Forecolor.ObjectThemeColor = msoThemeColorAccent1-6
  ' convert theme info to straight RGB
  .Line.Forecolor.RGB = .Line.Forecolor.RGB
End With