PDA

View Full Version : VBA for multi-colored pivot chart bars?



jsabo
01-23-2014, 08:04 AM
Hello

Trying to create a pivot chart (bar chart) from a pivot table. Excel doesnt seem to want to let me have multiple colored bars. Is there a way to do this in VBA during the creation of the chart? Thanks!

Bob Phillips
01-23-2014, 08:15 AM
If it ius all one data series then it would be the same colour. You can change the points like so


With ActiveChart.SeriesCollection(1)
With .Points(1).Format.Fill
.ForeColor.ObjectThemeColor = msoThemeColorAccent1
.ForeColor.TintAndShade = 0
.ForeColor.Brightness = 0
.Solid
End With
With .Points(2).Format.Fill
.Visible = msoTrue
.ForeColor.ObjectThemeColor = msoThemeColorText2
.ForeColor.TintAndShade = 0
.ForeColor.Brightness = 0.8000000119
.Transparency = 0
.Solid
End With
With .Points(3).Format.Fill
.Visible = msoTrue
.ForeColor.ObjectThemeColor = msoThemeColorText2
.ForeColor.TintAndShade = 0
.ForeColor.Brightness = 0.8000000119
.Solid
End With
End With

Andy Pope
01-27-2014, 09:37 AM
If you only have 1 series in the pivot chart then you should be able to format the series fill and enable "Vary color by points".

If you must use code then


Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

ActiveSheet.ChartObjects(1).Chart.ChartGroups(1).VaryByCategories = True

End Sub