i have the following code which should apply a specific colour to th
bar based on what the catagory is (as seen in my screenshot or th
table and chart)

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

[image: http://www.darkcity.nildram.co.uk/pivot1.jpg]

its basically colouring all the bars red, which is colourindex 3, whic
is the 'else' at the bottom, even though 6 of the 7 cases are there i
the chart (screenshot shows what it looks like when i colour the bar
manually). anyone know why its not picking up the names of th
different column groups properly? i.e. i want it to colour the "comm
& scada" bars a certain colour, but it doesnt seem to recognise tha
those bars are part of comms & scada group (i didnt write this cod
someone on here did so i dont know).

and one last thing, is there some place where i can put this code s
that it also runs if someone selects a different 'date of work' fro
the top (as seen in my screenshot) ? because at the moment selecting
different date puts it back to defaults and u have to click anothe
sheet then click the chart sheet again so that the chart_activate i
called to do the colouring etc

Thanks

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