Excel does not remember the correct color of inverted bar chart

[kirvis]

Hello,

I am creating a dashboard with a few bar charts, where the negative values have a different color then the positive values. To achieve this, I use the option "invert if negative" in the fill properties of the chart.

The problem is that when I save and re-open the file, the red color of the negative bars is put back to the default white-with-black-border.

Any idea on how I can make sure Excel remembers the correct color if the inverted bar charts?

Thanks,

Kirvis
Excel 2013

 

[Worf]

Hello and welcome to the Board

You could use a macro:

Sub FormatChart()
Dim s As Series, i%


Set s = ActiveSheet.ChartObjects("Chart 2").Chart.FullSeriesCollection(1)
For i = 1 To s.Points.Count
    Select Case InStr(s.Points(i).DataLabel.Text, "; -")            ' is it negative?
        Case 0
            s.Points(i).Format.Fill.ForeColor.RGB = RGB(5, 200, 10)
            s.Points(i).DataLabel.Format.Fill.ForeColor.RGB = RGB(180, 230, 140)
        Case Is > 0
            s.Points(i).Format.Fill.ForeColor.RGB = RGB(240, 4, 8)
            s.Points(i).DataLabel.Format.Fill.ForeColor.RGB = RGB(240, 150, 140)
    End Select
Next
End Sub