colouring pivotchart bars (using code)

[neowok]

what ive got is 7 catagories, and each has a planned and actual column
so the chart looks a bit like (ignore the dots its just for spacing)


3......._......._.............._..........._
2......| |......| |............| |..........| |
1___| |___| |______| |_____| |
...planned actual | planned actual
..........SMEP.........|.........L&E

what i need is to be able to reference "SMEP" and "L&E" so i can say i
catagory 1 = "SMEP" then colour those 2 bars this colour and move on t
the next one, if catagory 1 = "L&E" then colour it this colour and mov
on to the next etc etc. but itll also first have to check how man
catagories are actually IN the chart at the time because theres up to
set catagories depending on whether data exists for all 7 or not.

Basically on the main data table that all this info comes from, there
one of these 7 catagories as one column, and for it theres a planne
and actual column further accross.

I have code to colour the bars, but at the moment afaik i can onl
colour the bars one bar at a time, rather than colour the bars fo
"SMEP" i have to colour bar 1 and bar 2 like this

ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).Points(1).Select
ActiveChart.SeriesCollection(1).Points(1).ApplyDataLabel
ShowValue:=True
With Selection.Interior
.ColorIndex = 39
End With

So is there a way to say something like

with catagories (1 to end)
IF (catagory) = "L&E" THEN
set colour to 39 for the 2 bars that belong to that catagory and en
if
ELSE
if (catagory) ="SMEP" THEN
set colour to 34 for the 2 bars that belong to that catagory and en
if

(and so on for the 7 catagories.)

End wit

 

[neowok]

christ its been on here a whole day and had ONE view? cant be that har
heh