I am focusing on creating a spreadsheet that will compare students' grade data throughout the year. That means, on sheet 1 with be September - December, sheet 2: January - April, sheet 3: April - July.

I want to be able to compare whether has student has improved or declined in two categories Category 1: Effort (A*-E), Category 2: Attainment (1-6)

So student x could get B 2 for Maths in Autumn, but A 3 for Maths in Spring. I want my spreadsheet to highlight green for the improvement of B to A and then red for the decrease to 3.

I'm having trouble with creating something that will conditionally format the whole data across two sheets, I managed to do it for one student's attainment cells, but am not sure how to do it for all at once. Also, there is the issue of stating that A* is greater than an A etc.

Does anyone have any ideas about this?

Different students grades will be shown like this, with different subjects across the horizontal axis (so to speak)

Effort Attainment
C 3
A 2
B 3
C 3
B 2
C 3
A 2
B 3

Any help is greatly received!