PDA

View Full Version : coloring points in a series



uatuma
08-08-2006, 04:19 PM
How can I loop through a series in a X-Y scatter plot, and color points by value ranges? As an example, if a point is >2, color it red, if a point is <-2 color it green, etc...

Cyberdude
08-08-2006, 08:35 PM
Roughly how many points do you want to color? I'm guessing, but if there are, say, 300 points, that might take a lot of time to run the macro, plus it might take a lot of formatting resources.

Will the colors be spread around randomly, or will maybe the left half of a Series be red and the right half green? (I'm looking for ways to simplify.)

Cyberdude
08-08-2006, 08:58 PM
I threw this together and it seems to do the basic job. You can modify it to suit your needs.
Sub ColorChartPoints()
Dim N&, ColorVal&
ActiveSheet.ChartObjects("Chart 22").Activate
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(1).Select
For N = 1 To 10
ActiveChart.SeriesCollection(1).Points(N).Select

'Put decision logic here
If N > 5 _
Then ColorVal = Red _
Else ColorVal = Green

With Selection
.MarkerBackgroundColorIndex = ColorVal
.MarkerForegroundColorIndex = ColorVal
'Use the following if you want to
' .MarkerStyle = xlCircle
' .MarkerSize = 8
End With
Next N
'Deactivate the chart
ActiveWindow.Visible = False
Windows("MyWorkbookName.xls").Activate
Range("J15").Select
End Sub