Chart - Conditional formatting

[Guest]

Hi Everyone,

In order to change conditionally the colours of my data series in a Chart
(based on the colour set in a range named "pallet"), I used the following
code. The thing is that I need to work in a Clustered Column chart and
that the colour conditionality should be based on the Category label, not on
the Serie.

Example :

Category Serie
Flowers 5%
Clothes 2%
Food 15%
etc. I would like to select the colour according to the name of the category
(ie Flowers = yellow, Clothes = blue, etc).

Actually, the routine uses the serie values, as shown in line 4 (DataLabel)
of the code. How should I modify this routine to see it work properly with
the category name displayed on the X Axis ?

Sub SeriesColours()
Sheets("Sheet1").ChartObjects(1).Activate
For Each ppt In ActiveChart.SeriesCollection(1).Points
labl = ppt.DataLabel.Caption
Sheets("Sheet1").Range("pallet").Select
Selection.Find(What:=labl, After:=ActiveCell).Activate
scolor = ActiveCell.Interior.ColorIndex
Sheets("Sheet1").ChartObjects("Chart 1").Activate
ppt.Interior.ColorIndex = scolor
Next
Sheets("Sheet1").Cells(1).Select
End Sub

TIA for your cooperation

 

[Jon Peltier]

You need to match the category name, so do it like this:

With Sheets("Sheet1").ChartObjects(1).Chart.SeriesCollection(1)
For iPoint = 1 To .Points.Count
sCategory = WorksheetFunction.Index(.XValues, iPoint)
' now find the category in the lookup table

You should use something besides ppt for your point variable, since it will remind
many other folks of PowerPoint.

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