Search code examples
excelvba

VBA: How to Loop IF Statement through a Cell and Chart Series

Currently I have:

    Private Sub Worksheet_Calculate()
    ActiveSheet.ChartObjects("Chart").Activate
    Dim ch As Chart
    Set ch = ActiveChart
    If Range("D4").Value = 0 Then
    ch.SeriesCollection("Series1").Format.Line.ForeColor.RGB = RGB(255, 255, 255)
    ElseIf Range("D4").Value > 2 Then
    ch.SeriesCollection("Series1").Format.Line.ForeColor.RGB = RGB(0, 255, 0)
    ElseIf Range("D4").Value <= 2 And Range("D4").Value >= 1 Then
    ch.SeriesCollection("Series1").Format.Line.ForeColor.RGB = RGB(0, 200, 0)
    ElseIf Range("D4").Value < -2 Then
    ch.SeriesCollection("Series1").Format.Line.ForeColor.RGB = RGB(255, 0, 0)
    ElseIf Range("D4").Value < 0 And Range("D4").Value >= -1 Then
    ch.SeriesCollection("Series1").Format.Line.ForeColor.RGB = RGB(200, 0, 0)
    Else
    ch.SeriesCollection("Series1").Format.Line.ForeColor.RGB = RGB(0, 0, 0)
    End If
    End Sub

Which looks at a value in D4 and if D4 is between my bounds, it assigns the chart series a certain color (this value is not on the chart but rather an indicator used to classify the charts movement).

I want to loop this through 10 different Chart Series Collections, where in each Loop, I go down one cell. so go to Cell D5 check all conditions for Series 2, then Cell D6, check all conditions for Series 3

I am stuck here as I know how to do this for one half of this, but unsure how to loop into chartseries

Error I get:

enter image description here

enter image description here

Full code is exactly yours

Solution

  • Considering your comment, it can be a solution:

    Private Sub Worksheet_Calculate()
  Dim i As Integer
  Set rng = Sheets("Sheet1").Range("D4:D10")
  For i = 1 To 7
    ProcessSeries i, rng.Cells(i).Value
  Next
End Sub

Sub ProcessSeries(i As Integer, j As Double)
    With Worksheets("Sheet1").ChartObjects("Chart") _
      .SeriesCollection("Series" & i).Format.Line.ForeColor
        If j < -2 Then
            .RGB = RGB(255, 0, 0) ' for values up to -2
        ElseIf j < -1 Then
            .RGB = RGB(0, 0, 0) ' for values from -2 to -1
        ElseIf j < 0 Then
            .RGB = RGB(200, 0, 0) ' for values from -1 to 0
        ElseIf j = 0 Then
            .RGB = RGB(255, 255, 255) ' for value of 0
        ElseIf j < 1
            .RGB = RGB(0, 0, 0) ' for values from 0 to 1
        ElseIf j <= 2 Then
            .RGB = RGB(0, 200, 0) ' for values from 1 to 2
        Else
            .RGB = RGB(0, 255, 0) ' for values above 2
        End If
    End With
End Sub

    In Worksheet_Calculate, ProcessSeries is called for every "i" from 1 to 7. "i" and the value of related cell among "D4:D10" are provided for ProcessSeries.

    When ProcessSeries starts, it has "i" as the number of the data series (we expect series are named like "Series1".."Series7" what is done automatically when the chart is created). "j" gives the number from the related cell of "D4:D10".