问题描述
我有两种图表类型，将通过BI工具填充到Excel中.我需要根据一些规则为其中的可用系列上色.
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.
第一张图按年份显示支出(年份为系列)，并且历史记录的级别从几个月到24个月不等.这意味着我的24个月的数据分布在 年 2015、2016、2017年.明年，由于我连续滚动24个月，数据更改为2016、2017、2018年.
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.
无论数据集是什么，我都需要条形图数据中的最近年份(例如2017)以蓝色显示，其前一年(例如2016)以橙色显示，然后是该年份之前的年份(例如2015)灰色.
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.
我可能没有24个月的时间(例如新客户).如果只有六个月，则应用相同的着色逻辑，并且最近一年将需要以蓝色显示.
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.
支出图表
第二张图表显示基于性能的系列值.这些系列被称为准时"，宽容"和迟到".
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 或 2 或可能在给定的图表中都存在，没有可预测性.我需要VBA来确定可用的系列以及相应的颜色.
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.
性能图表
我将其他供稿中的代码拼凑在一起，因此并不是基础.我想我需要使用ForEach类型语法，因为我知道我需要遍历每个SeriesCollection对象.
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.
推荐答案
这是您第一个问题的简单小例程，将图表中的系列重新着色为蓝色，橙色和灰色，但反转了默认顺序:
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
这是您的第二个问题，根据系列名称将绿色分别为绿色，浅绿色和红色(根据需要调整RGB).您应该注意，某些人(大约8％的男性，少于1％的女性)可能会遇到区分绿色和红色的问题.因此，通常将蓝色和橙色用作首选配色方案.
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
这篇关于更改图表上系列的填充颜色的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！