VBA to Change Chart Data point colors?

[junebug]

Is there a way in a chart to change the color of a series point to red if the underlying data is <79%. I found code that changes all points in a series to one color but nothing would be conditional. Any ideas?

 

[Andrew Poulsom]

For a non-VBA approach:

http://peltiertech.com/Excel/Charts/format.html#CondChart

and VBA:

http://www.mrexcel.com/board2/viewtopic.php?t=63858

 

[junebug]

Thanks Mr. Poulsom. I elaborated on your code as I needed the line segment to change color not the data point marker if the data point was below 80% which is what is in cell R1. Below is what I came up with. You got me thinking in the right direction.

Sub ChangeDataPointColor()
'
Dim Pt As Point
    Dim Rng As Range
    Dim x As Integer
    Dim Y As Integer
    Dim MyColor
    Set Rng = ActiveSheet.Range("r4:r38")
    x = 1
    Y = 3
    
    
   Application.ScreenUpdating = False
     For Each Pt In ActiveSheet.ChartObjects("Chart 1").Chart.SeriesCollection(2).points
 ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(2).points(x).Select
    If Range("r" & Y).Value < Range("R1") Then
    MyColor = 3
    Else
    MyColor = 5
    End If
    
    With Selection.Border
        .ColorIndex = MyColor
        .Weight = xlThick
        .LineStyle = xlContinuous
    End With
 With Selection
        .MarkerBackgroundColorIndex = xlAutomatic
        .MarkerForegroundColorIndex = xlAutomatic
        .MarkerStyle = xlNone
        
        .MarkerSize = 5
        .Shadow = False
    End With
  x = x + 1
 
  Y = Y + 1
    Next Pt
  Application.ScreenUpdating = True
End Sub