Vlookup + sum

[RussChilds]

H!

Using Excel 2003. I have a 2 columns with pupil grades. Column A = Target Grade & B = Exam Grade. I have lookup table to convert the grade to a value.
Table values:
A= 5
B= 4
C =3
D =2
E =1
U =0

I want to conditional format so Green is for grades higher than target, orange is on target and red is below target.

For example: A1 = "B" & B2 = "C"

I'm using =VLOOKUP(A1,TBL,2,FALSE) & =VLOOKUP(B1,TBL,2,FALSE) to convert the grade to value. How do I then say I want B1 is greater than A1 to be Green?

Many thanks,
Russ

 

[Comfy]

Is the lookup table on the same sheet?

 

[RussChilds]

No its on another worksheet.

 

[Peter_SSs]

Manually apply red format to all the column B cells, then apply these CFs.

Excel Workbook
	A	B
1	Target	Grade
2	A	B
3	B	A
4	C	C
5	B	U
6	A	A
7	D	B
CF Grade

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2	1. / Formula is =B2=A2	Abc
B2	2. / Formula is =VLOOKUP(B2,TBL,2,0)>VLOOKUP(A2,TBL,2,0)	Abc