I have a sheet that runs a query on our ERP system. A second sheet is a
pivot table based on the returned data and a third sheet contains a graph
with buttons. Depending on which button is pressed, relevant data for that
department is returned. This is for yield analysis so I wanted all the
inputs to be one colour and all the outputs another. Every time the data
refreshes, the colours return to the default. I have a macro that reformats
the graph to the appropriate colours, but the issue now is that there were 4
ins and outs (ie 8 in total) when I wrote the macro, but this week there are
only 6 in total. The macro fails.

I am now trying to find away of drawing the graph with the default colours,
identifying how many points are on the graph then doing a FOR WHILE loop to
recolour the data points. ANy ideas how this could be done?

The line in my macro that fails is:

ActiveChart.SeriesCollection(1).Points(7).Select

as there are only 6 datapoits this week.

Regards

Alan Berry
find a way of identifying how many data points are on the graph then do it
using a a For While loop.

1. Count the points

Dim iPts as long
For iPts = 1 to ActiveChart.SeriesCollection(1).Points.Count
With ActiveChart.SeriesCollection(1).Points(iPts)
' blah blah
End With
Next

2. Don't select the points

ActiveChart.SeriesCollection(1).Points(7).Select
Selection.MarkerStyle = xlMarkerStyleDiamond

can be replaced by

ActiveChart.SeriesCollection(1).Points(7).MarkerSt yle = xlMarkerStyleDiamond

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


"Alan B" wrote in message
...
I have a sheet that runs a query on our ERP system. A second sheet is a
pivot table based on the returned data and a third sheet contains a graph
with buttons. Depending on which button is pressed, relevant data for
that
department is returned. This is for yield analysis so I wanted all the
inputs to be one colour and all the outputs another. Every time the data
refreshes, the colours return to the default. I have a macro that
reformats
the graph to the appropriate colours, but the issue now is that there were
4
ins and outs (ie 8 in total) when I wrote the macro, but this week there
are
only 6 in total. The macro fails.

I am now trying to find away of drawing the graph with the default
colours,
identifying how many points are on the graph then doing a FOR WHILE loop
to
recolour the data points. ANy ideas how this could be done?

The line in my macro that fails is:

ActiveChart.SeriesCollection(1).Points(7).Select

as there are only 6 datapoits this week.

Regards

Alan Berry
find a way of identifying how many data points are on the graph then do it
using a a For While loop.