title: Change Fill Color Of Series On Chart
body: <p>I have two types of charts that will be populated into Excel by a BI tool. I need to colour the available series in them according to some rules.</p>
  <p>The <strong>first chart</strong> shows expenditure by year (year is the series), and there are varying degrees of history from a few months, up to 24 months.  This means my 24 months of data is spread over <em><strong>years</strong></em> 2015, 2016, 2017. Next year this changes to 2016, 2017, 2018 as I’m keeping a rolling 24 months.</p>
  <p>Whatever the data set, I need the most recent year (e.g. 2017) in the bar chart data to be displayed in blue, the year before that (e.g. 2016) in orange, and then the year before that (e.g. 2015) in grey.</p>
  <p>It is possible I won’t have 24 months (e.g. new clients). If there are only six months, the same colouring logic applies, and that most recent year would need to display in blue.</p>
  <p><strong>Expenditure Chart</strong>
  <img src="https://i.stack.imgur.com/dQQEV.jpg" alt="" /></p>
  <p>The <strong>second chart</strong> shows series values based on performance. These series are called  ‘on time’, ‘in tolerance’ and ‘late’.</p>
  <p>Their colours need to be: ‘on time’ = mid green, ‘in tolerance’ = light green, ‘late’ = red.</p>
  <p>1 <strong>or</strong> 2 <strong>or</strong> all 3 of these series may be present in a given chart with no predictability. I need the VBA to determine which series are available and colour accordingly.</p>
  <p><strong>Performance Chart</strong><br />
  <img src="https://i.stack.imgur.com/5u8Yv.jpg" alt="" /></p>
  <p>I cobbled together code from other feeds, and isn’t a base to build from. I think I need to use ForEach type syntax, as I know I need to loop through each of the SeriesCollection objects.</p>