Excel 2007 : Ordering Conditional Format Rules

I am struggling to create a spreadsheet for teacher's to use that will use assessment data that is input to then determine the contional formatting for other cells; HOWEVER, I am running into problems determining in what order to place the rules.

Example: I need to enter data into column C and have that data coded based on whether or not a student has reached a benchmark score. The issue I am having is if a student is below the benchmark score in column C (which will be coded red, and that currently works). The problem I am experiencing is that if Column C is below, then I need need the cells in columns F and I (P1 and P2) to highlight yellow.

What I cannot seem to make work is that when data is entered into either column F or I, I need the conditional formatting for Green, Blue, and Red to replace the yellow highlighting.

HELP! I know nothing about VBA formatting, and a WONDERFUL person wrote the code for me for the attached spreadsheet. (The same one that I still need to tweak).

Any help that anyone could provide would be appreciated by about 300 teachers with whom I work, myself included.

I need to try to figure this out before tomorrow morning so that I can have data ready to review with teachers.

Thanks in advance for any help that you might provide!