Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
pivotchart colouring
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
pivotchart colouring
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/ _______ neowok < wrote: 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 --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
pivotchart colouring
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 -- Message posted from http://www.ExcelForum.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Duplicating PivotChart / Changing PivotChart data range | Charts and Charting in Excel | |||
colouring cell | Excel Discussion (Misc queries) | |||
Automatic colouring of cells | Excel Worksheet Functions | |||
Colouring rows | Excel Programming |