Conditional Formatting Headache

I have two questions about conditional formatting. 1 issue I have the output isn't correct, the 2nd I want to know if there just an easier way to accomplish the same output.

Question_1:

I am currently comparing two numbers (A1, A2)and I have the difference in a third cell (A3) I want to change Change the colour of Cell A1 based on the value of A3.

This is what I have and it is all coming up either green or red.

AND(A3>5.01,A3<-10.01) Colour == Amber

A3<-10.01 Colour == Red

AND(A3=0,A3>-5) Colour == Green

A3 >0 Colour == Blue



Question_2

I have 7 possible grades and all grades have a point 1,2 and 3. So A1,A2,A3 ETC

I want to colour the current grade to see if it is above or below their predicted grade. My issue is that as there are 21 possibilities I am creating conditional formatting for each possibility against each possibility. Both for if it is above and below. Below is a snippet just for the A1,A2,A3 to see if it is above the predicted grade.

Can anyone think of anything quicker to accomplish this?

TIA!






=IF(AND(S4="A1",U4="A2"),TRUE,IF(AND(S4="A1",U4="A3"),TRUE,IF(AND(S4="A1",U4="B1"),TRUE,IF(AND(S4="A1",U4="B2"),TRUE,IF(AND(U4="A1",S4="B3"),TRUE,IF(AND(S4="A1",U4="C1"),TRUE,IF(AND(S4="A1",U4="C2"),TRUE,IF(AND(S4="A1",U4="C3"),TRUE,IF(AND(S4="A1",U4="D1"),TRUE,IF(AND(S4="A1",U4="D2"),TRUE,IF(AND(S4="A1",U4="D3"),TRUE,IF(AND(S4="A1",U4="E1"),TRUE,IF(AND(S4="A1",U4="E2"),TRUE,IF(AND(S4="A1",U4="E3"),TRUE,IF(AND(S4="A1",U4="F1"),TRUE,IF(AND(S4="A1",U4="F2"),TRUE,IF(AND(S4="A1",U4="F3"),TRUE,IF(AND(S4="A1",U4="G1"),TRUE,IF(AND(S4="A1",U4="G2"),TRUE,IF(AND(S4="A1",U4="G3"),TRUE,IF(AND(S4="A1",U4="F"),TRUE,IF(AND(S4="A1",U4="U"),TRUE,IF(AND(S4="A2",U4="A3"),TRUE,IF(AND(S4="A2",U4="B1"),TRUE,IF(AND(S4="A2",U4="B2"),TRUE,IF(AND(U4="A2",S4="B3"),TRUE,IF(AND(S4="A2",U4="C1"),TRUE,IF(AND(S4="A2",U4="C2"),TRUE,IF(AND(S4="A2",U4="C3"),TRUE,IF(AND(S4="A2",U4="D1"),TRUE,IF(AND(S4="A2",U4="D2"),TRUE,IF(AND(S4="A2",U4="D3"),TRUE,IF(AND(S4="A2",U4="E1"),TRUE,IF(AND(S4="A2",U4="E2"),TRUE,IF(AND(S4="A2",U4="E3"),TRUE,IF(AND(S4="A2",U4="F1"),TRUE,IF(AND(S4="A2",U4="F2"),TRUE,IF(AND(S4="A2",U4="F3"),TRUE,IF(AND(S4="A2",U4="G1"),TRUE,IF(AND(S4="A2",U4="G2"),TRUE,IF(AND(S4="A2",U4="G3"),TRUE,IF(AND(S4="A2",U4="F"),TRUE,IF(AND(S4="A2",U4="U"),TRUE,FALSE)))))))))))))))))))))))))))))))))))))))))))

=IF(AND(S4="A3",U4="B1"),TRUE,IF(AND(S4="A3",U4="B2"),TRUE,IF(AND(s4="A3",u4="B3"),TRUE,IF(AND(S4="A3",U4="C1"),TRUE,IF(AND(S4="A3",U4="C2"),TRUE,IF(AND(S4="A3",U4="C3"),TRUE,IF(AND(S4="A3",U4="D1"),TRUE,IF(AND(S4="A3",U4="D2"),TRUE,IF(AND(S4="A3",U4="D3"),TRUE,IF(AND(S4="A3",U4="E1"),TRUE,IF(AND(S4="A3",U4="E2"),TRUE,IF(AND(S4="A3",U4="E3"),TRUE,IF(AND(S4="A3",U4="F1"),TRUE,IF(AND(S4="A3",U4="F2"),TRUE,IF(AND(S4="A3",U4="F3"),TRUE,IF(AND(S4="A3",U4="G1"),TRUE,IF(AND(S4="A3",U4="G2"),TRUE,IF(AND(S4="A3",U4="G3"),TRUE,IF(AND(S4="A3",U4="F"),TRUE,IF(AND(S4="A3",U4="U"),

All of the data I am using for work is confidential.

That is why I posted the actual formula I am using for the conditional formatting.

It is a conditional formatting formula.

It should be checking if A3 is between or equal to 0 and -5.
The conditional formatting will then make A3 Green if TRUE.

I have just uploaded a small sheet with the example formatting.

Thank you.