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)
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:
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)
apply a similar formula in my conditional formatting rules, I add an extra condition with another operator, for example...
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...
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...