identifying a chart column individually in code

[papa jonah]

Let me try a different approach.
I currently use a clusterd column chart that is created automatically
with code.
Presently, I need to select each column manually to format them
according to a convention that we like at work.
Each column that represents a subset of a given group is colored to
represent the group of which it is a subset.

Each column that is of a different group gets a different color.

I think I can automate this process if I can tell the code to look at
column 1, for example. Then column 2, and so forth through the entire
set of columns. If I can do that, I can provide an IF then statement
in the colorindex definition that will color it according to the proper
grouping.

Is there any way to either define a numbering sequence to the columns,
or is there already one that I can tap into?

TIA

 

[Andy Pope]

Hi,

The following code will change the colorindex of every column in a chart.

Sub x()
Dim chtTemp As Chart
Dim intSeries As Integer
Dim intPoint As Integer

With ActiveChart
For intSeries = 1 To .SeriesCollection.Count
With .SeriesCollection(intSeries)
For intPoint = 1 To .Points.Count
.Points(intPoint).Interior.ColorIndex = _
intSeries * intPoint + 4
Next intPoint
End With
Next intSeries
End With

End Sub


Cheers
Andy

 

[papa jonah]

Andy,
That gets me much closer to where I need to be. Is there a way, as I
am going through the loop, to refer back to the row of data that the
individual column is derived from?

What I am trying to accomplish is this:
I have a table that resembles (in part)

A B C
1 1 1A 10
2 1 1C 15
3 1 1D 9
4 2 2A 12
5 3 3A 17
6 3 3B 4
7 4 4D 14

What I want is to have the data from Column B along the x axis while
the values in Column C ploted as bars with the values along the Y axis.
However, I want all of the bars that correspond to rows with a a common
value in Column A to be the same color. In this example rows 1-3 would
be red, row 2 would be blue, rows 5 & 6 would be green, and so on.

So in order to get your code to work, I need to be able to get the
point reviewed in the loop to refer back to its source row.

Did that make sense?

Thanks for your help.

 

[Andy Pope]

Hi,

In order to link back to the cells you will need to parse the series
formula. See John's tips on this.
http://j-walk.com/ss/excel/tips/tip83.htm

Another approach maybe to use extra data series to plot specific
colours. See Jon's example of conditional charts to see if its
appropriate for your problem.
http://peltiertech.com/Excel/Charts/ConditionalChart1.html

Cheers
Andy