Excel VBA, choosing chart color based on series value comparison

Question

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.

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

David Zemens David Zemens · Accepted Answer · 2016-11-04T16:21:15

Did you try something like:

Case Is > .SeriesCollection(2).Values()(Counter)

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

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

Excel VBA, choosing chart color based on series value comparison

2 Answers