Conditional formatting target grade against actual grade

Hi everyone,

I have a markbook where I colour code the grades students receive on assessments against a target grade. I would love to use conditional formatting to do this and cut down time.

The grades: A*, A, B, C, D, E, F, G, U.

Say a student's target grade was in A1, and their assessment grade was in B1, I would want B1 to be colour coded so that:

On target (e.g. matching A1) = Green
1 below target (e.g. one grade less than A1) = Orange
2 below target (e.g. 2 grades less than A1) = Red

Is there anyway to do this without adding in additional columns for differences between grades etc?

Thank you so much for your time!

Hello
You would need 3 Conditional Formatting formulas, for example:

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

The named range 'Grades' is list of your Grades. Apply these CF formulas to your marking range.

DBY

Ahhhh thank you! You are a genius and have just saved me HOURS thank you so much!

Glad to have helped. Hope all your students are green!