Excel VBA，根据系列值比较选择图表颜色
我已经使用了一些代码来为excel图表着色很多年了，并且效果很好（尽管可能有更好的方法可以做到）。图表包含2个系列，第一个系列带有值，第二个系列带有目标。目标不会变色，但是vba会根据vba中的硬编码目标循环遍历第一个系列和颜色。
I have some code I have used to color excel charts with for quite a few years and it has worked well, (although there are likely better ways to do it). The charts contain 2 series, the first series with a value and the second with a goal. The goal does not get colored but the vba loops through the first series and colors according to hard coded goals in the vba.
我现在遇到的问题是，我添加了一个图表，该图表的目标可以逐月更改，因此进行硬编码是行不通的。如何使用相同的理论，但直接将系列1数据与系列2数据进行比较以确定颜色（案例1系列点>系列2点等）。我尝试了多种方法但均未成功，因此，我们将不胜感激。下面是经过验证的技术的代码。
The problem I have now is that I have added a chart that has a goal that can change month to month so having the hard coding doesn't work. How can I use the same theory but compare series 1 data directly to series 2 data to determine the color, (Case Is series 1 point > series 2 point, etc). I have tried a number of ways without success so any assistance would be greatly appreciated. below is the code for the proven technique.
Private Sub Worksheet_Activate()
Dim cht As Object
Dim p As Object
Dim V As Variant
Dim Counter As Integer
For Each cht In ActiveSheet.ChartObjects
Counter = 0
V = cht.Chart.SeriesCollection(1).Values
For Each p In cht.Chart.SeriesCollection(1).Points
Counter = Counter + 1
Select Case V(Counter)
'Case Is = 1
'p.Shadow = False
'p.InvertIfNegative = False
'p.Fill.OneColorGradient Style:=msoGradientVertical, Variant:=3, _
' Degree:=0.78
'p.Fill.Visible = True
'p.Fill.ForeColor.SchemeColor = 5
Case Is < 0.98
p.Shadow = False
p.InvertIfNegative = False
p.Fill.OneColorGradient Style:=msoGradientVertical, Variant:=3, _
Degree:=0.78
p.Fill.Visible = True
p.Fill.ForeColor.SchemeColor = 3
'Case Is < 0.98
'p.Shadow = False
'p.InvertIfNegative = False
'p.Fill.OneColorGradient Style:=msoGradientVertical, Variant:=4, _
' Degree:=0.38
'p.Fill.Visible = True
'p.Fill.ForeColor.SchemeColor = 6
Case Is <= 1
p.Shadow = False
p.InvertIfNegative = False
p.Fill.OneColorGradient Style:=msoGradientVertical, Variant:=3, _
Degree:=0.78
p.Fill.Visible = True
p.Fill.ForeColor.SchemeColor = 10
End Select
Next
Next
End Sub
您是否尝试过以下方法：
Did you try something like:
Case Is> .SeriesCollection（2）.Values（）（计数器）
也进行了修改，以消除一些明显的冗余（如果需要循环，请输入 >和一个计数器变量，例如，当并行循环几个集合/数组时，似乎更好的IMO是按索引循环，而不是 For Each _object _
Also revised to get rid of some apparent redundancy (if need a loop and a counter variable, e.g., when looping several collections/arrays in parallel), it seems better IMO to just loop by index, rather than For Each _object_ with a separate counter.
Private Sub Worksheet_Activate()
Dim cht As Object
Dim p As Object
Dim V As Variant
Dim Counter As Integer
For Each cht In ActiveSheet.ChartObjects
Counter = 0
With cht.Chart
V = .SeriesCollection(1).Values
For Counter = 1 to.SeriesCollection(1).Points.Count
'Assign your Point object, if needed elsewhere
Set p = .SeriesCollection(1).Points(Counter)
Select Case V(Counter)
Case Is > .SeriesCollection(2).Values()(Counter)
'DO STUFF HERE.
'Add other cases if needed...
End Select
Next
End With
Next
End Sub
并且除非需要数组 V 中的值>出于某些其他原因，可以进一步减少该值：
And unless you need the values in an array V for some other reason, this can be further reduced:
Private Sub Worksheet_Activate()
Dim cht As Object
Dim p As Object
Dim val1, val2
Dim Counter As Integer
For Each cht In ActiveSheet.ChartObjects
Counter = 0
With cht.Chart
For Counter = 1 to.SeriesCollection(1).Points.Count
'Assign your Point object, if needed elsewhere
Set p = .SeriesCollection(1).Points(Counter)
' extract specific point value to variables:
val1 = .SeriesCollection(1).Values()(Counter)
val2 = .SeriesCollection(2).Values()(Counter)
Select Case V(Counter)
Case val1 > val2
'DO STUFF HERE.
'Add other cases if needed...
End Select
Next
End With
Next
End Sub