VBA Line Chart conditional Formatting help

Question

I am new to VBA and learning as I go.  

I am trying to setup a line chart that changes the color of the line based on if the values hits a certain target.  The target is 83.  If it is 83 or above the line should be green, if less than 83 the line should be red,  I was able to create a code that changes the color of the line, but not with the conditional formatting.

I get a run-time 1004 error. Invalid Paramater

The code is:

Sub LineColor()

 Dim ser As Series

 Set ser = ActiveSheet.ChartObjects("Chart 1").Chart.SeriesCollection(1)

 Dim i As Integer

If i >= 83 Then

     ser.Points(i).Border.Color = RGB(32, 160, 32)

     Else

     ser.Points(i).Border.Color = RGB(255, 0, 0)

   End If

End Sub

 I am sure the solution is simple, but as I said I am new to this and have no real clue.  Thanks in advance for your help.

Answer 1

If it is 83 or above the line should be green, if less than 83 the line should be red

I am sure the solution is simple, but as I said I am new to this and have no real clue. 

If one knows how to do it, it is always simple. ;-)

Have a look at the code below, I hope it's helpful.

Andreas.

Sub Test()

  Dim CO As ChartObject

  Dim S As Series

  Dim P As Point

  Dim i As Long

  Dim Data

  'Create some data

  Worksheets.Add

  Range("A1") = "Nr"

  With Range("A2:A20")

    .Formula = "=RAND()"

    .Value = .Value

  End With

  'Create a chart above this cells

  With Range("C2:H20")

    Set CO = ActiveSheet.ChartObjects.Add(.Left, .Top, .Width, .Height)

  End With

  With CO.Chart

    'Set chart type and associate the data

    .ChartType = xlLineMarkers

    .SetSourceData Range("A1").CurrentRegion

    'Get the 1st series

    Set S = .SeriesCollection(1)

  End With

  With S

    'Read the Y-Values (Data becomes a 1D array)

    Data = .Values

    For i = 1 To UBound(Data)

      'The points are in the same order 1..x

      Set P = .Points(i)

      'Change the color of the markers based on the value

      If Data(i) < 0.5 Then

        P.MarkerBackgroundColor = vbRed

      Else

        P.MarkerBackgroundColor = vbGreen

      End If

    Next

  End With

End Sub