vba 如何以编程方式更改 Excel 2007 图表中系列的线条颜色
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/289122/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I programmatically change the line colour for a series in a chart in Excel 2007
提问by geometrikal
I have a chart with a series that denotes a large set (1000's) of discrete measurements. Some of these are bad measurements and I want to colour the line for the series based on another set of data that describes how accurate the measurements are. Bad measurements should be red and good measurements green and the in between on some kind of gradient from red to yellow to green.
我有一个带有一系列表示大量（1000 个）离散测量值的图表。其中一些是错误的测量，我想根据描述测量准确度的另一组数据为系列的线条着色。不好的测量应该是红色的，好的测量应该是绿色的，介于两者之间的某种从红色到黄色到绿色的渐变。
This should be able to be programmed with VBA however I have no idea what to do. Can anyone give me some hints?
这应该可以用 VBA 进行编程，但是我不知道该怎么做。谁能给我一些提示？
回答by Fionnuala
It is easy enough to colour a chart line in VBA. Here are some notes.
在 VBA 中为图表线着色很容易。这里有一些注意事项。
Dim cht As Chart
Dim sc As Series
Dim blnBad As Boolean
Dim j
j = 85 'RGB orange '
blnBad = False
'This is a chart called Chart 1, it would be possible '
'to use the charts collection '
Set cht = ActiveSheet.ChartObjects("Chart 1").Chart
'A chart is composed of series of data ... '
For Each sc In cht.SeriesCollection
' ... that you can iterate through to pick up '
' the individual data values, or a data range. '
' Values in this case. '
For i = LBound(sc.Values) To UBound(sc.Values)
' That can be checked against another set of '
' values in the range Bad. '
With ActiveSheet.Range("Bad")
' So, look for the value ... '
Set c = .Find(sc.Values(i), lookat:=xlWhole, LookIn:=xlValues)
' and if it is found ... '
If Not c Is Nothing Then
' ... then set the Bad flag '
blnBad = True
End If
End With
Next
' So, this range contains a Bad value '
' and we will colour it red ... '
If blnBad Then
sc.Border.Color = RGB(255, 0, 0)
' ... not forgetting the markers '
sc.MarkerForegroundColor = RGB(255, 0, 0)
Else
' Otherwise, use an increasingly yellow colour '
sc.Border.Color = RGB(255, j, 0)
sc.MarkerForegroundColor = RGB(255, j, 0)
j = j + 30 ' getting more yellow
' Debug.Print j ' uncomment to see j in the immediate window '
End If
blnBad = False
Next
End Sub
回答by Elijah
Are you locked into VBA? One way this could be accomplished is by open up your OOXML .xlsx document archive (it is actually a Zip archive). Then you have free access to the XML data that makes up the document itself. This could be ran through an XSL style sheet or any other script of your choosing and then rezipped.
你被锁定在 VBA 吗？实现此目的的一种方法是打开您的 OOXML .xlsx 文档档案（它实际上是一个 Zip 档案）。然后您可以自由访问构成文档本身的 XML 数据。这可以通过 XSL 样式表或您选择的任何其他脚本运行，然后重新压缩。
