Conditional formatting of lines in a chart
 

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.

Conditional formatting of lines in a chart
 

Points on a line are probably easier:

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

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"jonny" wrote in message
...
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.

Conditional formatting of lines in a chart
 

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

--
John Mansfield
http://cellmatrix.net





"jonny" wrote:

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.