Change Chart Series Colour

Changing the chart colour based on the colour of a range can be used to to highlight a condition within a chart.  In the following example I will add a colour to a chart series name after the procedure has been executed.  The items which have a value 10 or greater have the colour blue and the items lower than 10 have been assigned the colour red.  I changed the colours slightly in each chart for effect.

After the procedure has run then charts look like the following.  Any item 10 or above is highlighted with the colour in the

You will notice the Hedge and House are in Red in the first chart while person 3 is blue in the second chart.  The following is the code which will loop through all of the charts in the active worksheet and change the colour of the chart series based on the colour of the series name.

Option Explicit

Sub ChangeColour() 'Excel VBA change chart colour.
Dim rng As Range
Dim str As String
Dim j As Integer
Dim i As Integer

For j=1 To ActiveSheet.ChartObjects.Count
str=ActiveSheet.ChartObjects(j).Name
With Sheet1.ChartObjects(str).Chart.SeriesCollection(1)
Set rng=ActiveSheet.Range(Split(Split(.Formula, ",")(1), "!")(1))
For i=1 To rng.Cells.Count
.Points(i).Format.Fill.ForeColor.RGB=ThisWorkbook.Colors(rng.Cells(i).Interior.ColorIndex)
Next i
End With
Next j
End Sub


An important point to note is this procedure will not work with conditional formatting. If you want to add colour to the cell which make up the chart series then one way to do this would be to loop through the range or apply an autofilter on the range and colour based on the results of the loop or the filter. As the ranges are small here is some simple looping code which will colour the cells which are greater than or equal to 10.

Sub ColourSeries() 'Excel VBA change chart colour.
Dim r As Range
Dim rng As Range

Set rng=[B10:B14]

For Each r In rng
If r.Offset(, 1) >=10 Then r.Interior.Color=vbBlue Else r.Interior.Color=vbRed
Next r
End Sub


The above cover just the first range in the file but can be adapted to your data. There is a similar article using a different technique to make chart series stand out here Change Bar Colour.