The case:

Excel 2013, Multiple cheets with multiple charts of different type. Several charts doesn't show well will the defaut color automatically generated by Excel. -> The idea is to modify these graphics only with a specific color combination (modifying the color of the series)

I'm able to modify the color of each series of 1 chart using the UI. but there are many charts so doing them with a simple macro would speed these repeative steps.

I turned on the macro recording to capture the change of the color of a selected serie in a selected chart.

The generated code is:

    ActiveChart.Legend.LegendEntries(1).Select
    With Selection.Format.Fill
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoThemeColorAccent3
        .ForeColor.TintAndShade = 0
        .ForeColor.Brightness = -0.25
        .Transparency = 0.3000000119
        .Solid
    End With

When I execute the macro, Excel trigger an error:

Execution error -2147467259 (80004005)

method "Fill" of object "ChartFormat" has failed   (this is a translated error message, as I run a french version)

Any idea why Excel cannot run its own generated code ?

Thanks in advance.

• Edited by gdhont 18 hours 24 minutes ago

It's a bug in the macro recorder, need to apply formats to the LegendKey,

Dim lge As LegendEntry
Dim lgk As LegendKey
    Set lge = ActiveChart.Legend.LegendEntries(1)
    Set lgk = lge.LegendKey
    With lgk.Format.Fill
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoThemeColorAccent3
        .ForeColor.TintAndShade = 0
        .ForeColor.Brightness = -0.25 ' note this fails in 2007
        .Transparency = 0.3000000119
        .Solid
    End With

In passing it may be better to apply formats directly to individual series. But note although typically the x in .SeriesColelction(x) and LegendEntries(x) is typically the same it may not always be the same index value

• Edited by Peter Thornton (Excel MVP 2008-13) 20 hours 4 minutes ago

Very surprisingly the following code (also generated by the macro recording) works ok.

(I selected the serie inside the chart instead of selecting the serie in the legend)

    ActiveChart.FullSeriesCollection(1).Select
    With Selection.Format.Fill
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoThemeColorAccent3
        .ForeColor.TintAndShade = 0
        .ForeColor.Brightness = -0.25
        .Transparency = 0.3000000119
        .Solid
    End With

If an MVP or Microsoft official come accross this, I suggest further testing as it is very likely a bug (with the first set of code generated by Excel)

Thanks Peter for pointing that alternative.

I did investigated in parallel too and came with an alternate code (generated by the macro recording functionnality) see my reply to my initial post.

As I mentioned there's a bug with the macro recorder to show the step between the LegendEntry and the LegendKey. The macro recorder with Charts and Shapes was extremely flaky in 2007 but it has improved considerably with subsequent versions, and now correctly records most format actions such as to a series. That said the recorder is still not perfect but don't expect any fixes with existing versions.

• Proposed as answer by George.Zhao CHNMicrosoft contingent staff, Moderator 1 hour 17 minutes ago