Teacher Gradebook; Shading cells based on comparison between grade and target grades

[MrPolitics]

Hi folks,

I've seen a couple of threads on this over the years but can't seem to apply any of those solutions to my problem.

I have a tracking sheet in which my student's assessment grades are input. The TARGET GRADE for each student (A*-U) is kept in column U.

My assessment columns then carry on across the workbook. I already use a VLOOKUP to assign the grade, so for each assessment I have three columns (Grade, Mark, %).

What I want to create is a conditional format that compares the grade for each assessment to the student's target grade and highlights the cell based on the relationship (e.g. green for same or better, red for lower)

I'd also like to be able to apply this easily to each grade column in the spreadsheet for each assessment.

An example of my workbook is below.

Thanks for any help you can give!!

teacher gradebook example.xlsx

[mrice]

Set up conditional formatting to use a formula for on track or better

=MATCH($W2,$X$31:$X$37,0) >= MATCH($O2,$X$31:$X$37,0)

applies to

=$W$2:$W$28

and for needs improvement

=MATCH($W2,$X$31:$X$37,0) < MATCH($O2,$X$31:$X$37,0)

[MrPolitics]

Thanks so much Martin, you're an absolute hero.