Colour Coding ???

[mrsshadow]

I am a school teacher and I want to colour code cells based on whether my students are achieving their target grade or not. So, if a students target grade is a C and they are acheiving below this grade I want their grade to be colour coded RED, if they are currently achieving a C, I want their grade to be colour coded AMBER, if their currently achieving above a C, I want their grade to be colour coded GREEN.
However, each student in my class has a different target grade so the formulas would need to be different?
Any ideas????

Thanks!!!

[martindwilson]

1.post an example sheet please with sample targets and grades.
2. teach them sooooooooooooooooo well they all have above average grades and simply colour whole sheet green

[mrsshadow]

Ok, so here goes for the explanation...

If student A's current AS Grade is greater than their Target Grade then I want the grade in the current AS Grade column to appear Green, if their Current AS Grade is equal to their target grade then I want the grade in the current AS grade column to appear amber, if their current AS grade is lower than their target grade then I want the grade in the current AS grade column to appear Red, but all students grades are different.

Can you help???

[martindwilson]

firstly =AVERAGE(L3:T3:AB3) doesnt make sense i think it should be
=AVERAGE(L3,T3,AB3)

[mrsshadow]

OK, Yes you are right, I have changed that! Whats next?

[martindwilson]

conditional formats are(formula is)
1.
=CODE(E3)<CODE(C3) green
2.
=CODE(E3)=CODE(C3) amber
3.
=CODE(E3)>CODE(C3) red

[mrsshadow]

How do I add that to the formula that is already there? I thought I could use "format" then "conditional formatting" then add in the conditions? But I was unsure what to type as conditions?

[mrsshadow]

I have done what you suggested but the cell remains green no matter whether the grade is higher, lower or equal to? Do you have any more suggestions? Thanks for all your help so far...

[martindwilson]

yep go to conditional formatting
and use those threE formulas as your conditions
see example