I have actually got it finished by just using excel to sum each of the 17 week
periods.

Week 1 to 17 (E4:E20) sums to BR4
Week 2 to 18 (E5:E21) sums to BR5
Week 3 to 19 (E6:E22) sums to BR6

And so on.

Then Conditional format checks the range in BR4:BR53 and if any cell exceeds my
specified range then the cell colour is changed.