Conditional formatting pie charts

I got this mostly by recording a macro and then wrapping it in a loop. I made a pie chart with equal sized wedges (Column C) and then assign colors based on whether the space is Full or Empty.

I trigger the event off a change in Cells B2:B12 to run the macro FillColor.

I’m an ex-C programmer myself. Tell me, does it bother you as much as it bothers me about how “loose” VBA is with variables? .

I have my personal naming conventions for my coding. One of them is that any variable starting with i has something to do with rows and any variable that starts with j has something to do with columns. I picked k as the next letter of the alphabet to be my first choice as an index or counter.

Each wedge of the chart is controlled by the “index” of the point: ActiveChart.FullSeriesCollection(1).Points(k).Format.Fill there are 11 wedges in the pie, so k here goes from 1 to 11.

I also have the status in 11 rows in column B from B2:B12. So one loop does two tasks. It reads the status: If sh.Cells(k + 1, 2) = "Full" Then

And if sets the corresponding wedge color With ActiveChart.FullSeriesCollection(1).Points(k).Format.Fill.

I added in the change event as an afterthought. Based on the cell that is changed, I could have read that specific status and set that specific wedge rather than loop through all 11 items. In theory, this would be more efficient code. But with only 11 things to loop through and only one thing to set or calculate per item, I decided to leave “good enough” alone.

-=o=-

I just read your follow-on posts.

I can’t duplicate your error. You say it occurs here: With ActiveChart.FullSeriesCollection(1).Points(k).Format.Fill.

There are two things I can think of if you have changed anything.

One is that the chartobject has a different name: sh.ChartObjects("Chart 1").Activate

The other is that you are plotting a different FullSeriesCollection.

OR

I am using Excel 2013 and you are using Excel 2010. It could be that some things have been “depreciated” as Microsoft puts it which means they’ve changed it and the codes isn’t backwards compatible.

I suggest you record a macro that selects and individual pie wedge and change its color. That’s what I did to get the syntax. This should also tell you what chart object and FullSeriesCollection is active.

P.S. please feel free to change to name of the macro to FillColour .