Locking chart colors using VBA

[MoAnab]

Hi guys, would really appreciate the help
I have created an excel model on reporting on initiatives which is updated regularly by other people in the organization; however I am having trouble locking the chart colors whenever the data is refreshed.

I created the below chart using a pivot table which summarizes how many initiatives are "Red", "Green", etc.. I am trying to lock the colors of the chart through VBA to the following
if "Completed" = (0,176,240)
if "Red" = (192,0,0)
if "Green" (146,208,80)
if "Amber" = (255,192,0)
if "Grey" = (217,217,217)

Chart Name is "Performance_Summary"
Note sure if this helps but the criteria to calculate status is below:

Would really appreciate the help because its driving me crazy!

 

[vmjan02]

This "should" work, though I haven't used chart templates enough to know for sure. I did a simple test just now, which was promising.
Format one of your charts exactly how you want it. Right click on the chart, click Save as Template from the pop-up menu, then give the template a descriptive name, and click Save.
Next time you change data and the chart loses its formatting, right click the chart, choose Change Chart Type, click on Templates, select your template, and click OK.

 

[MoAnab]

@vmjan02 Hey thanks for replying, however my understanding is that the templates will save on my PC hence if I handover the excel file to someone else the format will be lost right?

 

[vmjan02]

No, I it will not.

 

[MoAnab]

@vmjan02 still seems not to be working as once I deleted all the raw data and input it again the format changes due to arrangement of the labels (Red, Green, etc..) in the pivot table. Solution would either be to have the labels not disappear in case there is no corresponding value to a label such as "Red" meaning none of the initiatives are labeled as Red or writing macro that will format chart according to labels in pivot table.

 

[vmjan02]

Ok,
Here is the code with range. If it is any help for you it will be great. I did a little part test and it did worked, color did not change.

Sub ColorBySeriesName()
  Dim rPatterns As Range
  Dim iSeries As Long
  Dim rSeries As Range

  Set rPatterns = ActiveSheet.Range("A1:A4") 'You can change the range here.
  With ActiveChart
    For iSeries = 1 To .SeriesCollection.Count
      Set rSeries = rPatterns.Find(What:=.SeriesCollection(iSeries).Name, _
          LookAt:=xlWhole)
      If Not rSeries Is Nothing Then
        .SeriesCollection(iSeries).Format.Fill.ForeColor.RGB = _ 'colour here.
            rSeries.Interior.Color
      End If
    Next
  End With
End Sub