Morning All,

The following function is used to loop through all line charts on each
sheet of a report. I was hoping that when is finds any negative points
it would colour in that point on the chart red - it does work nu the
points are coloured black .....any help appreciated.

Regards
Jason.


'=============================================
Public Function FormatGraph(myWorksheet As String)

Sheets(myWorksheet).Select

Dim myChartObject As ChartObject
For Each myChartObject In Sheets("Daily Results").ChartObjects
myChartObject.Select

Dim myPoint As Integer, valArray

With ActiveChart.SeriesCollection(1)
valArray = .Values
For myPoint = 1 To .Points.Count
Select Case valArray(myPoint)
Case Is 0
With .Points(myPoint)
.Interior.ColorIndex = xlAutomatic
End With
Case Is = 0
With .Points(myPoint)
.Interior.ColorIndex = xlAutomatic
End With
Case Is < 0
With .Points(myPoint)
.Interior.ColorIndex = 25
.Interior.Pattern = xlSolid
End With
End Select
Next
End With

Next myChartObject
Cells(1, 1).Select

End Function 'FormatGraph
'=============================================

Assuming you are using a default palette ColorIndex 25 is dark blue, which
might look like black depending on your monitor, settings, environment and
eyesight.

Maybe you want colorindex 3, or if you are not sure user might have
customized the palette
..Interior.Color = vbRed

Alternatively why not check the "Invert if negative" option and dispense
with your macro (though not red).

Regards,
Peter T



"WhytheQ" wrote in message
...
Morning All,

The following function is used to loop through all line charts on each
sheet of a report. I was hoping that when is finds any negative points
it would colour in that point on the chart red - it does work nu the
points are coloured black .....any help appreciated.

Regards
Jason.


'=============================================
Public Function FormatGraph(myWorksheet As String)

Sheets(myWorksheet).Select

Dim myChartObject As ChartObject
For Each myChartObject In Sheets("Daily Results").ChartObjects
myChartObject.Select

Dim myPoint As Integer, valArray

With ActiveChart.SeriesCollection(1)
valArray = .Values
For myPoint = 1 To .Points.Count
Select Case valArray(myPoint)
Case Is 0
With .Points(myPoint)
.Interior.ColorIndex = xlAutomatic
End With
Case Is = 0
With .Points(myPoint)
.Interior.ColorIndex = xlAutomatic
End With
Case Is < 0
With .Points(myPoint)
.Interior.ColorIndex = 25
.Interior.Pattern = xlSolid
End With
End Select
Next
End With

Next myChartObject
Cells(1, 1).Select

End Function 'FormatGraph
'=============================================

On Aug 26, 10:39*am, "Peter T" <peter_t@discussions wrote:
Assuming you are using a default palette ColorIndex 25 is dark blue, which
might look like black depending on your monitor, settings, environment and
eyesight.

Maybe you want colorindex 3, or if you are not sure user might have
customized the palette
.Interior.Color = vbRed

Alternatively why not check the "Invert if negative" option and dispense
with your macro (though not red).

Regards,
Peter T

"WhytheQ" wrote in message

...



Morning All,

The following function is used to loop through all line charts on each
sheet of a report. I was hoping that when is finds any negative points
it would colour in that point on the chart red - it does work nu the
points are coloured black .....any help appreciated.

Regards
Jason.

'=============================================
Public Function FormatGraph(myWorksheet As String)

Sheets(myWorksheet).Select

Dim myChartObject As ChartObject
For Each myChartObject In Sheets("Daily Results").ChartObjects
* * * * * *myChartObject.Select

* * * * * *Dim myPoint As Integer, valArray

* * * * * *With ActiveChart.SeriesCollection(1)
* * * * * * * *valArray = .Values
* * * * * * * *For myPoint = 1 To .Points.Count
* * * * * * * * * *Select Case valArray(myPoint)
* * * * * * * * * * * *Case Is 0
* * * * * * * * * * * * * *With .Points(myPoint)
* * * * * * * * * * * * * * * *.Interior.ColorIndex = xlAutomatic
* * * * * * * * * * * * * *End With
* * * * * * * * * * * *Case Is = 0
* * * * * * * * * * * * * *With .Points(myPoint)
* * * * * * * * * * * * * * * *.Interior.ColorIndex = xlAutomatic
* * * * * * * * * * * * * *End With
* * * * * * * * * * * *Case Is < 0
* * * * * * * * * * * * * *With .Points(myPoint)
* * * * * * * * * * * * * * * * * .Interior.ColorIndex = 25
* * * * * * * * * * * * * * * * * *..Interior.Pattern = xlSolid
* * * * * * * * * * * * * *End With
* * * * * * * * * * * *End Select
* * * * * * * *Next
* * * * * *End With

Next myChartObject
Cells(1, 1).Select

End Function * * * * * *'FormatGraph
'=============================================- Hide quoted text -

- Show quoted text -



cheers Peter

atb
jason