On Jun 8, 7:30*pm, "Shane Devenshire"
wrote:
Hi,

Here is what you need:

Create a range in the spreadsheet that contains all the letter grades in
order:

A+
A
A-
B+
B
B-
C+
C
C-
D+
D
D-
F+
F
F-

You may not need A+ and F-. *Suppose this is in the range L1:L15.

Assume your grades start in B2 and extend to the right *say to J2.
Highlight the range C2:J2 (skipping B2) and choose Format, Conditional
Formatting, Formula is (from the first drop down). *Enter the formula:
=MATCH(C2,$L$1:$L$15,0)<MATCH(B2,$L$1:$L$15,0)
set a color and click Add. *Choose Formula is and enter the second formula:
=MATCH(C2,$L$1:$L$15,0)MATCH(B2,$L$1:$L$15,0)
set a color.

You can make the formulas shorter by defining a range name, call it F for
the range L1:L15, then your formulas become:
=MATCH(C2,F,0)<MATCH(B2,F,0)
and
=MATCH(C2,F,0)MATCH(B2,F,0)

If you want to keep the range L1:L15 reference on a different sheet than the
conditionally formatted cell you will need to use the range name approach
mentioned above. *(When L1:L15 is selected click in the Name Box and type
the name you want to use.)

If you don't want a range in the spreadsheet to be used at all than choose
the Insert, Name, Define command and enter a name in the first box then in
the Refers to box enter:
={"A+";"A";"A-";"B+";"B";"B-";"C+";"C";"C-";"D+";"D";"D-";"F+";"F";"F-"}

Cheers,
Shane Devenshire
Microsoft Excel MVP
Joinhttp://setiathome.berkeley.edu/and download a free screensaver and
help search for life beyond earth.

"staffrmj" wrote in message

...



Can I use conditional formatting to compare text values in Excel?

I want to compare student grade performance over time and want to show
if a student's grade has increased or decreased e.g. A - B would be a
decrease, B - A would be an increase.

Thanks- Hide quoted text -

- Show quoted text -

Thanks a lot - it worked a treat!