Conditional formatting of lines in a chart

[Guest]

Is there any way that one can change the color of a line in the chart
depending on the value of some other cell in the spreadsheet e.g., if a
certain condition is true, the line (or point on the line) is blue and if it
is false the line (or point on the line) is red.

 

[Jon Peltier]

Points on a line are probably easier:

http://peltiertech.com/Excel/Charts/ConditionalChart1.html

- Jon

 

[Guest]

Jon's method is preferred, but if you feel like using VBA something like
below might be tailored to work for you. Assuming an embedded chart, copy
the procedure into a sheet module containing that chart. In this example the
procedure will change the color of the first series based on a TRUE or FALSE
value entered into cell A1.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Not Intersect(Target, Range("A1")) Is Nothing Then

Dim Cht As ChartObject
Dim Srs As Series

'The name of the chart (i.e. Cht1)
Set Cht = ActiveSheet.ChartObjects("Cht1")

'The number of the series that you want to change i.e. 1
Set Srs = Cht.Chart.SeriesCollection(1)

If IsEmpty("A1") = True Then
Exit Sub
ElseIf Range("A1").Value = True Then
Cht.Activate
'The color number of the series if value = TRUE
Srs.Border.ColorIndex = 5
Srs.MarkerBackgroundColorIndex = 5
Srs.MarkerForegroundColorIndex = 5
ElseIf Range("A1").Value = False Then
Cht.Activate
'The color number of the series if value = FALSE
Srs.Border.ColorIndex = 7
Srs.MarkerBackgroundColorIndex = 7
Srs.MarkerForegroundColorIndex = 7
End If

Range("A2").Select

End If

End Sub