Change Chart Colour Scheme

[Rudi]

Hi,

I have multiple workbooks with several charts in each.
I have code to open each workbook and select the charts, but I'd like to update the overall colour scheme of each chart to a different colour (see image)

The recorder gives me the .ChartColor = 11 (property), however, when i run it in my code, it debugs with Object does not support this property??
Any idea how I can change the default Excel chart colours to a theme colour as in the image?

My code:

Code: Select all

    For iChart = 1 To ActiveSheet.ChartObjects.Count
    Set chtob = ActiveSheet.ChartObjects(iChart)
    With chtob
        .Activate
        .ChartColor = 11
    End With
    Next

TX

1.jpg

[Rudi]

Actually...my silly mistake.
I solved it!

The .ChartColor property is actually a property of the ActiveChart not of ChartObjects!
This code works:

Code: Select all

    For iChart = 1 To ActiveSheet.ChartObjects.Count
        ActiveSheet.ChartObjects(iChart).Select
        ActiveChart.ChartColor = 11
    Next
    Range("A1").Select

I'm not sure if one *needs* to select the chart in order to change the ChartColor?
I select Range("A1") to deactivate the chart after the macro runs.

[HansV]

ChartColor must be new in Excel 2013 - it's not available in Excel 2010.

As usual, you don't need to select:

Code: Select all

    For iChart = 1 To ActiveSheet.ChartObjects.Count
        ActiveSheet.ChartObjects(iChart).Chart.ChartColor = 11
    Next iChart

or

Code: Select all

    Dim cht as ChartObject
    For Each cht In ActiveSheet.ChartObjects
        cht.Chart.ChartColor = 11
    Next cht

[Rudi]

Excellent. I see it is hooked up to the "Chart" Object
Whoever came up with, The Chart object is an object inside the ChartObject object

I was aware of it...just forgot!
TX

BTW: If ChartColor is new, is there an alternative for 2010? The code I am setting up is actually supposed to run in Excel 2010.
TX

[HansV]

A ChartObject is a shape that contains a chart.

I don't think there is an equivalent to ChartColor in Excel 2010 or earlier. You can set the ThemeColorScheme but this applies to the entire workbook and hence to all charts in the workbook. And you can set the ChartStyle but that changes other aspects in addition to the color scheme, such as 3D effects.

[Rudi]

TX.
Appreciate the insights.

The code is actually to update a few hundred financial sheets with different colour etc.
I have code to loop through all the files in a folder, find cells with certain colours, and update them to other colours...update fonts and certain measurements of the printout. There are a few charts in each file too, so I plan to create a colour scheme and then run the code to update the charts with that colour scheme. If I go this route I will need to do the updating of these sheets on my laptop. It is only a once off, so if Excel 2013 (with the chartcolor property can make it easier), then that's the way to go.

Cheers