Hi there, I'm trying to create some conditional formatting rules for a grade sheet.
I currently have a setup worksheet which has a named range ("grades") which runs through a grading system (8a,8b,8c,7a,7b etc) and assigns each with a numeric value (1 through 22 since there are 22 grades)

Essentially, my conditional format needs to calculate the difference between 2 grades by looking up their numeric value and colouring based on differences I set.

I can successfully calculate the difference between 2 grades on my worksheet by using the following formula:

=(VLOOKUP(I4,grades,2,FALSE))-(VLOOKUP(F4,grades,2,FALSE))

This essentially looks up the numeric value for the 'Target Grade' (I4) and the numeric value for the 'actual grade' (F4) and calculates the difference between them (2 - since the grades are '4c' and '3b', they are within '2 grades' of each other)

When I apply a similar formula in my conditional formatting rules, I add an extra condition with another operator, for example...

=(VLOOKUP($I$3,grades,2,FALSE))-(VLOOKUP($F$3,grades,2,FALSE))>2

The formula gives an error if I remove the $ characters. In essence, the above formula should colour the target cell Red because the student has strayed too far away from their target grade...

I have applied an opposite rule to colour the cell green to check my logic (<=2 at the end) but the cells are not colouring.

My other uncertainty is how to refer to the 'current' cell in the conditional formatting statement. So, something like this...


=(VLOOKUP(CURRENTCELL,grades,2,FALSE))-(VLOOKUP(CURRENTCELL,grades,2,FALSE))>2

Any help would be greatly appreciated. I can script in VB if there is an easier way to do that, I can write in VB.NET but not really looked into excel before like this...