Restore chart default colours after deleting series

Question

Hi I can't believe I can't find an answer to this but I have looked.

In Excel I want to restore the default colour palette order to a chart that I have deleted some series from.

Ie if my colour palette goes: red, green, blue, yellow, orange, grey and I originally have a chart with 6 lines they are in this order.

If I then delete the green, blue and yellow lines:

I am hoping there is a way to update the chart so instead of the remaining red, orange, grey I can return it to red, green, blue - Ie what I would get if I recreated the final chart from scratch:

Obviously I could recreate from scratch or manually change colours but I do this fairly frequently and its often a lot easy to select all columns and delete the ones I don't want than to go through and make a chart line by line.

I'm using corporate excel with minimal permissions so no addins please. Just want to know if this is possible in standard excel environment.

Answer 1

I did some testing, most macros that just adjust the colors seem to create a mess, especially if you want to adjust the chart by using the "chart design->change colours" option. So I went for "remove all series and add them again" approach. This works fine on my PC (Office 365). Select the chart and run this macro (you can assign a shortcut key to make this faster: https://www.excelcampus.com/vba/keyboard-shortcut-run-macro/ ):

Sub ChartRemoveReAddData()

    Dim DataArr() As String
    Dim n As Long
    
    Set AChart = Application.ActiveChart
    If Not AChart Is Nothing Then
        'First add series to DataArr
        i = 0
        For Each Ser1 In AChart.SeriesCollection
            sFmla = Ser1.Formula
            ReDim Preserve DataArr(i)
            DataArr(i) = sFmla
            i = i + 1
        Next Ser1
        'Again, but now reverse to remove them
        For n = AChart.SeriesCollection.Count To 1 Step -1
            AChart.SeriesCollection(n).Delete
        Next n
        AChart.ClearToMatchStyle
        AChart.ClearToMatchColorStyle
        For i = 0 To UBound(DataArr)
            Set S = AChart.SeriesCollection.NewSeries
            S.Formula = DataArr(i)
        Next i
    End If
   
End Sub

And a bit more detail to the colouring: Excel has 10 theme colours (plus 2 for hyperlinks) that you can find if you click e.g. the font colour or background colour button. The last 6 of that overview are the relevant ones for charts. The are named Accent 1 to Accent 6 (https://learn.microsoft.com/en-us/office/vba/api/Office.MsoThemeColorIndex). And as you can see those colours are showing up when you select your chart and select "chart design - change colours". So e.g. ChartColor = 11 (in VBA) means that your chart shows the colours Accent 1 (first series), Accent 3 (second series), Accent 5 (3rd series).

Answer 2

A manual procedure is to save the graph model and reload it once the series have been deleted.

A VBA solution might be similar to this one:

Sub SubParallelMeridianFormat()
    
    'Declarations.
    Dim IntCounter01 As Integer
    Dim Ser01 As Series
    Dim Char01 As Object
    Dim LngColourPalette(1 To 6) As Long
    
    'Setting LngColourPalette.
    LngColourPalette(1) = vbRed
    LngColourPalette(2) = vbGreen
    LngColourPalette(3) = vbBlue
    LngColourPalette(4) = vbYellow
    LngColourPalette(5) = RGB(255, 165, 0)
    LngColourPalette(6) = 16711680
    
    'Setting Char01.
    Set Char01 = ActiveSheet.Shapes("Graph 1")
    
    'If there are not enouth colour, the macro is terminated.
    If Char01.Chart.SeriesCollection.Count > UBound(LngColourPalette) Then
        MsgBox "The graph contains " & Char01.Chart.SeriesCollection.Count & " series while only " & UBound(LngColourPalette) & " colours have been specified. No changes will be applied. Add more colours to the code and try again.", vbCritical + vbOKOnly, "Not enouth colour"
        Exit Sub
    End If
    
    'Changing colours.
    For Each Ser01 In Char01.Chart.SeriesCollection
        IntCounter01 = IntCounter01 + 1
        Ser01.Format.Line.ForeColor.RGB = LngColourPalette(IntCounter01)
    Next
    
End Sub

You might need to edit the settings of LngColourPalette and Char01.

In order to obtain a list of colour from a pre-existing graph, you might use this code:

Sub SubColourList()
    
    'Declarations.
    Dim Ser01 As Series
    Dim Char01 As Object
    Dim IntCounter01 As Integer
    
    'Setting Char01.
    Set Char01 = ActiveSheet.Shapes("Graph 1")
    
    'Reporting colours.
    For Each Ser01 In Char01.Chart.SeriesCollection
        IntCounter01 = IntCounter01 + 1
        Debug.Print "LngColourPalette(" & IntCounter01 & ") = "; Ser01.Format.Line.ForeColor.RGB
    Next
    
End Sub

Here you have a list of colour constants for VBA and a list of colour codes in multiple coding style.

Answer 3

This approach will go through the chart, capture the formulas of the existing series, delete the existing series, and add them back from the stored formulas. In so doing, Excel will reapply the default colors.

I think it's simpler than the other proposed answers, even the one marked "Answer", and it will actually do what is requested.

Sub RebuildChartWithDefaultSeriesColors()
  With ActiveChart.SeriesCollection
    Dim nSrs As Long
    nSrs = .Count
    Dim vSrsFmla As Variant
    ReDim vSrsFmla(1 To nSrs)
    Dim iSrs As Long
    For iSrs = nSrs To 1 Step -1
      vSrsFmla(iSrs) = .Item(iSrs).Formula
      .Item(iSrs).Delete
    Next
    For iSrs = 1 To nSrs
      With .NewSeries
        .Formula = vSrsFmla(iSrs)
      End With
    Next
  End With
End Sub