VBA Array to Change Line Chart Colors

Part of my responsibilities at work is building the end-of-month financial packet that is presented to management. That packet includes a variety of line charts that show trends in financial and statistical metrics. Many of the graphs start with a base year and require adding an additional line as the year turns over. For example, in 2009 and given a base year of 2006 each line chart would have five lines (2006, 2007, 2008, 2009 plan, and 2009 actual). Now that the year has turned over, each graph will have six lines (2006, 2007, 2008, 2009, 2010 plan, and 2010 actual).

This may or may not be the most efficient process, but I prefer to keep the line series order as follows:

• Series 1 = Current Year (2010) -> line color = red
• Series 2 = Current Year Plan (2010) -> line color = blue
• Series 3 = Prior Year (2009) -> line color = green
• Series 4 = Prior Year - 1 (2008) -> line color = silver
• Series 5 = Prior Year - 2 (2007) -> line color = silver
• Series 6 = Prior Year - 3 (2006) -> line color = silver

This logic can present a problem in that as an additional series is added, the series order needs to be renumbered and the line colors have to be redone. To make the process easier, we use the following macro to change the series colors. It uses an array to store the color index numbers for each series.

Sub Color_Series()

    Dim arrColors(1 To 6) As Integer
    
    Dim Cht As ChartObject
    
    Dim i As Integer
     
    arrColors(1) = 3    'Series 1 - Red = Current Year (2010)
    arrColors(2) = 5    'Series 2 - Blue = Current Year Plan (2010)
    arrColors(3) = 10  'Series 3 - Green = Prior Year (2009)
    arrColors(4) = 16  'Series 4 - Silver = Prior Year (2008)
    arrColors(5) = 15  'Series 5 - Silver = Prior Year (2007)
    arrColors(6) = 14  'Series 6 - Silver = Prior Year (2006)
    
    Set Cht = Worksheets("Sheet1").ChartObjects("Chart01")
    
    For i = LBound(arrColors) To UBound(arrColors)
    
        Cht.Chart.SeriesCollection(i).Border.ColorIndex = arrColors(i)
        
    Next i
    
End Sub

Rather than looping through an array, you could just as easily refer to each of the series individually. To do so, the code would look like this:

Sub Color_Series()

    Dim Cht As ChartObject
    
    'Series 1 - ColorIndex = 3 = Red = Current Year (2010)
    'Series 2 - ColorIndex = 5 = Blue = Current Year Plan (2010)
    'Series 3 - ColorIndex = 10 = Green = Prior Year (2009)
    'Series 4 - ColorIndex = 16 = Silver = Prior Year (2008)
    'Series 5 - ColorIndex = 15 = Prior Year (2007)
    'Series 6 - ColorIndex = 14 = Silver = Prior Year (2006)
    
    Set Cht = Worksheets("Sheet1").ChartObjects("Chart01")
    
    Cht.Chart.SeriesCollection(1).Border.ColorIndex = 3
    Cht.Chart.SeriesCollection(2).Border.ColorIndex = 5
    Cht.Chart.SeriesCollection(3).Border.ColorIndex = 10
    Cht.Chart.SeriesCollection(4).Border.ColorIndex = 16
    Cht.Chart.SeriesCollection(5).Border.ColorIndex = 15
    Cht.Chart.SeriesCollection(6).Border.ColorIndex = 16
        
End Sub