Conditional format of bars based on values of PowerPoint Waterfall Charts

[Hamond]

Hi

I have charts created in Powerpoint (not Excel) using one series with 6 data points. I want to conditional format those bars for each data point based on whether the value for each data point/bar is positive (set to green) or negative (set to red).


I have several charts across multiple sheets that I would like to apply this to but can't get beyond the first stage. The following codes lets me set the colours when selecting a single chart so I know how to set the colours but not how to do the evaluation:



Sub Testbarcolour()
Dim p As Point
Dim s As Series
Dim c As Chart


Set c = ActiveWindow.Selection.ShapeRange(1).Chart
Set s = c.SeriesCollection(1)


With s
.Points(1).Format.Fill.ForeColor.RGB = RGB(0, 176, 240)
.Points(2).Format.Fill.ForeColor.RGB = RGB(0, 88, 240)

.Points(6).Format.Fill.ForeColor.RGB = RGB(0, 176, 240)
End With


End Sub

I tried to add in the evaluation like this line but it does not work


If .Values(1) > 0 Then .Points(1).Format.Fill.ForeColor.RGB = RGB(25, 176, 24)


Would be great if someone can help with this.

Thanks

Hamond

[John Wilson]

My experience is that the PowerPoint vba object model for Waterfall charts (which are fairly new) is not well implimented. I don't think what you need can be done for this type of chart but I could be wrong (haven't found a way yet though)

[Hamond]

Thanks for info John.

It appears someone was able to retrieve values using the DataLabel property at the link here but not sure if we are referring to the same thing.

https://stackoverflow.com/questions/49411763/get-category-names-of-waterfall-chart

Hamond

[John Wilson]

Unless I have misunderstood it's not agreat workaround though (changing the chart type) and I don't think you could change the fill colors.