Trying to create a school grade sheet in order to check progress.

Hello,

I am trying to create an excel spreadsheet that can show me if a child is performing above predicted, on target grade or below it. I have set it up so that if an assessment grade is the same as a child's target grade, then the box goes green. I would like to do the same, but with higher than predicted (blue) and also for those not achieving target grade (orange if just one sub-grade away, red in two sub-grades and purple if a full grade away. I have encountered some problems.

1. I used conditional formatting for the colour, but i can't see how I can apply this to all the boxes I want it to, rather than just the box I created it with.
2. The colours keep overwriting each other.
3. I am using a separate page to show the grade boundaries, and I can't seem to link them to the first page.

Year 7 template.xlsx