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

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 2

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