pivotchart colouring

[neowok]

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

 

[Jon Peltier]

I'll answer #2 first. Put the macro into a regular code module, and call
it from the Chart_Activate event procedure. You can also call it from
the Tools > Macro command.

#1. As pointed out in the charting group, you have a compound category
label, because the labels come from two columns of the pivot table. You
need to experiment with the various strings to see what is needed for
the code to recognize the labels.

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

i have the following code which should apply a specific colour to the
bar based on what the catagory is (as seen in my screenshot or the
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, which
is the 'else' at the bottom, even though 6 of the 7 cases are there in
the chart (screenshot shows what it looks like when i colour the bars
manually). anyone know why its not picking up the names of the
different column groups properly? i.e. i want it to colour the "comms
& scada" bars a certain colour, but it doesnt seem to recognise that
those bars are part of comms & scada group (i didnt write this code
someone on here did so i dont know).

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

Thanks

 

[neowok]

Thanks I will reply to the charting group now.

I do have the code in chart_activate, howver this only works when yo
first click the chart, not when you use any of the dropdowns on
pivotchart. Using the dropdowns removes your formatting so I need t
re-apply the macro whenever the dropdowns are used but cant work ou
where to call the macro from in order to do this.

Thank