identifying a chart column individually in code

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

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 wrote:
> 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, Microsoft MVP - Excel
http://www.andypope.info

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.

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/...nalChart1.html

Cheers
Andy

papa jonah wrote:
> 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, Microsoft MVP - Excel
http://www.andypope.info