Chart - conditional formatting
 

Hi Everyone,

In order to change conditionally the colours of my data series in a Chart
based on the colours set in a range named "pallet", I used the following
code. The only problem is that I need to work in a Clustered Column chart and
the colour conditionality is 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