Change Fill Color Of Series On Chart
Here's a simple little routine for your first question, recoloring the series in your chart blue, orange, and gray but reverse the default order:
Sub ReverseDefaultColors()
Dim iSrs As Long, nsrs As Long
If ActiveChart Is Nothing Then
MsgBox "Select a chart and try again.", vbExclamation, "No Active Chart"
Else
With ActiveChart
nsrs = .SeriesCollection.Count
' work backwards from last series
For iSrs = nsrs To 1 Step -1
Select Case nsrs - iSrs
Case 0 ' last series
.SeriesCollection(iSrs).Format.Fill.ForeColor.ObjectThemeColor = _
msoThemeColorAccent1
Case 1 ' next to last series
.SeriesCollection(iSrs).Format.Fill.ForeColor.ObjectThemeColor = _
msoThemeColorAccent2
Case 2 ' etc.
.SeriesCollection(iSrs).Format.Fill.ForeColor.ObjectThemeColor = _
msoThemeColorAccent3
End Select
Next
End With
End If
End Sub
Here's another for your second question, coloring green, light green, and red based on series name (adjust RGB as required). You should note that some people (about 8% of males, less than 1% of females) may have problems distinguishing between green and red. For this reason, blue and orange are often used as a preferred color scheme.
Sub ColorGreenToRed()
Dim iSrs As Long, nSrs As Long
If ActiveChart Is Nothing Then
MsgBox "Select a chart and try again.", vbExclamation, "No Active Chart"
Else
With ActiveChart
nSrs = .SeriesCollection.Count
For iSrs = 1 To nSrs
' only format series whose names are found
Select Case LCase$(.SeriesCollection(iSrs).Name)
Case "on time"
.SeriesCollection(iSrs).Format.Fill.ForeColor.RGB = _
RGB(0, 176, 80) ' Green
Case "in tolerance"
.SeriesCollection(iSrs).Format.Fill.ForeColor.RGB = _
RGB(146, 208, 80) ' Light Green
Case "late"
.SeriesCollection(iSrs).Format.Fill.ForeColor.RGB = _
RGB(255, 0, 0) ' Red
End Select
Next
End With
End If
End Sub
Related videos on Youtube
Giles Walker
Updated on June 04, 2022Comments
-
Giles Walker over 1 year
I have two types of charts that will be populated into Excel by a BI tool. I need to colour the available series in them according to some rules.
The first chart shows expenditure by year (year is the series), and there are varying degrees of history from a few months, up to 24 months. This means my 24 months of data is spread over years 2015, 2016, 2017. Next year this changes to 2016, 2017, 2018 as I’m keeping a rolling 24 months.
Whatever the data set, I need the most recent year (e.g. 2017) in the bar chart data to be displayed in blue, the year before that (e.g. 2016) in orange, and then the year before that (e.g. 2015) in grey.
It is possible I won’t have 24 months (e.g. new clients). If there are only six months, the same colouring logic applies, and that most recent year would need to display in blue.
Expenditure Chart
The second chart shows series values based on performance. These series are called ‘on time’, ‘in tolerance’ and ‘late’.
Their colours need to be: ‘on time’ = mid green, ‘in tolerance’ = light green, ‘late’ = red.
1 or 2 or all 3 of these series may be present in a given chart with no predictability. I need the VBA to determine which series are available and colour accordingly.
Performance Chart
I cobbled together code from other feeds, and isn’t a base to build from. I think I need to use ForEach type syntax, as I know I need to loop through each of the SeriesCollection objects.
-
Tim Williams about 6 yearsIf you added some sample data and a screenshot of a typical plot that's going to increase your chances of getting help.
-
Giles Walker about 6 yearsThanks for your advice - I have added some images showing what I need to get to, and the pivots currently being linked from.
-
jsotola about 6 yearslooking at the charts, it does not look like you need VBA for this. the columns always retain the same color. just modify the style of each chart to fit the requirement.
-