stacked bar graphs

[greg7468]

Hello all,
Is there a way of conditionally formatting the fill colour of seperat
segments on a stacked bar graph.
My chart has a different colour for each segment at the moment. I wa
wondering if it was possible to say have the segment of the chart t
be the same fill colour as the source data cell fill colour.
I am using Office 2000 and am fairly new to VBA.

Thanks in advance,

Greg

 

[Jon Peltier]

You can have VBA see the color of the cell, and apply it to the chart
element, but if the cells have conditional formatting, it's gonna cost
you a great deal of anguish working out the details. I've done it with
manually formatted cells.

If you don't have so many segments to keep track of, you could expand
the conditional charting approach on this page, with one set of
conditional columns in the worksheet per series.

http://peltiertech.com/Excel/Charts/ConditionalChart1.html

For a macro approach, which checks the value of each point and formats
the bar accordingly, you could expand this macro (a sneak preview for a
page I haven't written yet, maybe when the July crunch has passed):

Sub ColorPoints()
Dim iPtCt As Long
Dim iPtIdx As Long

With ActiveChart.SeriesCollection(1)
iPtCt = .Points.Count
For iPtIdx = 1 To iPtCt
Select Case WorksheetFunction.Index(.XValues, iPtIdx)
Case "A"
.Points(iPtIdx).Interior.ColorIndex = 1
Case "B"
.Points(iPtIdx).Interior.ColorIndex = 2
Case "C"
.Points(iPtIdx).Interior.ColorIndex = 3
Case "D"
.Points(iPtIdx).Interior.ColorIndex = 4
Case Else
.Points(iPtIdx).Interior.ColorIndex = 5
End Select
Next
End With
End Sub

I did it for X values, but you could just as easily do it for Y values,
replacing the Case "A", Case "B" with Case y > 10, Case y>5 etc. You'll
also want to enclose this inside

For Each Srs in ActiveChart.SeriesCollection
....
Next

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