colouring pivotchart bars using code

I have the floowing pivotchart (see attached)

what I want to do is colour these bars based on the catagory at the
bottom using code. i.e. if catagory is Comms & SCADA then colour it xx
colour.

now sometimes there is 6 catagories, sometimes 7 (but never more than
7) so what i need also, is to put that code inside a loop which says
something like for column 1 to last, if the catagory for this column =
"comms & SCADA" then colour it xx colour.

ive got as far as colour the bar using this code for each

ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).Points(1).Select
ActiveChart.SeriesCollection(1).Points(1).ApplyDat aLabels
ShowValue:=True
With Selection.Interior
..ColorIndex = 33
End With

however the catagories are not always in the same order (and there
arent always the same number) so at the moment im having to manually
add the code and colours for each of the bars. what i want to do is
have code that will colour the bars in based on what the delivery group
is. there are 7 set groups which will not change and as you can see
from the graph, this weekthere is only 6 listed.

basically i need the code which says
from first bar to last bar do
if bar is for delivery group "xxxx" then set colour to xx
if bar is for delivery group "yyyy" then set colour to yy
etc etc with one if for each of the 7 groups.

Thanks

Attachment filename: pivot1.jpg
Download attachment: http://www.excelforum.com/attachment.php?postid=580609
---
Message posted from http://www.ExcelForum.com/

its not working, no idea why but its not picking up the cases at all,
heres the code i have

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 "SMEP Projects & Commissioning"
..Points(iPtIdx).Interior.ColorIndex = 4
..Points(iPtIdx).ApplyDataLabels ShowValue:=True
Case "Special Projects Infrastructure"
..Points(iPtIdx).Interior.ColorIndex = 38
..Points(iPtIdx).ApplyDataLabels ShowValue:=True
Case "Permanent Way & Track"
..Points(iPtIdx).Interior.ColorIndex = 36
..Points(iPtIdx).ApplyDataLabels ShowValue:=True
Case "Special Projects - Property"
..Points(iPtIdx).Interior.ColorIndex = 35
..Points(iPtIdx).ApplyDataLabels ShowValue:=True
Case "L&E, Structures & Depot"
..Points(iPtIdx).Interior.ColorIndex = 34
..Points(iPtIdx).ApplyDataLabels ShowValue:=True
Case "JNUP & 7th Car"
..Points(iPtIdx).Interior.ColorIndex = 37
..Points(iPtIdx).ApplyDataLabels ShowValue:=True
Case "Comms & SCADA"
..Points(iPtIdx).Interior.ColorIndex = 39
..Points(iPtIdx).ApplyDataLabels ShowValue:=True
Case Else
..Points(iPtIdx).Interior.ColorIndex = 3
End Select
Next
End With

its basically colouring all the bars red, which is colourindex 3, which
is the 'else' at the bottom, even though all 7 of those cases are there
as far as i can see.

Ive also got this extra bit of code in another sub for another table
which has more than one series so i can add data labels to it all (but
dont need to change the bar colours) but it seems to take a couple of
seconds to run but it does work, is there any way to make it more
efficient? also can i add a different font colour to the 'data label'?
I can do it manually but not sure of the code for it.

Dim iPtCt As Long
Dim iPtIdx As Long
Dim iPtCt2 As Long
Dim iPtIdx2 As Long

iPtCt2 = ActiveChart.SeriesCollection.Count
For iPtIdx2 = 1 To iPtCt2
With ActiveChart.SeriesCollection(iPtIdx2)
iPtCt = .Points.Count
For iPtIdx = 1 To iPtCt
..Points(iPtIdx).ApplyDataLabels ShowValue:=True
Next
End With
Next
End Sub

Thanks


---
Message posted from http://www.ExcelForum.com/