4

New to pivot charts and their associated vba. But I'm attempting to change the colours of my bar graph depending on which province they belong to. I've been unable to use conditional formatting in a chart so I attempted a macro. Since I can't upload pictures my table looks a bit like this:

Prov/Name/Period/Value
ON: Name1, Dec14, 100
    Name2, Dec13, 200
BC: Name1, Dec14, 400
    Name2, Dec13, 600
SK: Name1, Dec14, 100
    Name2, Dec13, 2000

My pivot chart displays this data in a bar graph with each bar being the same colour. I'd like all series that fall under the BC row field to display yellow to highlight them against the rest. So far I've been doing this by hand, and I've attempted to make a vba macro, but don't even know where to start in referencing the Prov row field of the chart.

Community
  • 1
  • 1
Rob
  • 65
  • 9
  • 1
    have you tried recording a macro while you perform the steps manually? This may give you the necessary syntax to access the chart elements associated with BC. My guess would be that you would have to loop through the chart series in some way and distinguish which is the BC one to work with it. – Scott Holtzman Sep 04 '15 at 13:59
  • Yes. But unfortunately I cannot select the group on the pivot chart. So I cannot find the range or equivalent for the BC group. Color changes and series selection I can find, but not how to identify BC as a group within the series – Rob Sep 04 '15 at 14:04
  • can you share your XL file? – Scott Holtzman Sep 04 '15 at 14:07
  • I'm unsure of how to do that/whether I have privileges to attach a file here – Rob Sep 04 '15 at 14:15
  • if you can provide a link to it from dropbox or google drive or something, that would work. – Scott Holtzman Sep 04 '15 at 14:24

1 Answers1

0

Ok, so the item on the chart that you need to reference is the XValues. XValues can be an array or a range. For some reason though you can't loop through it with xvalues(x). Instead you need to assign it to a variable and THEN you can loop through it and check for a specific value. Consider the following code. Oh and you may need to attached the code to any events where the pivotchart changes.

Dim ch As ChartObject, Counter As Integer, XValueArr() As Variant

Set ch = Sheet1.ChartObjects("Chart 1") 'this is your pivotchart

XValueArr() = ch.Chart.SeriesCollection(1).XValues 'assign the XValues property to an array

'loop through the array and check the value
For Counter = LBound(XValueArr) To UBound(XValueArr)
    If Left(XValueArr(Counter), 2) = "BC" Then
    ch.Chart.SeriesCollection(1).Points(Counter).Format.Fill.ForeColor.ObjectThemeColor = msoThemeColorAccent2
    End If
Next Counter
Jess-eye-ca
  • 61
  • 5