Conditional Formatting

[fosteri01]

Hi

Have just spent 2 hours trying in vain to solve this one. All I want is to compare two cells that contain a grade and color fill one of them depending on its relationship (ie greater than, equal to or less than). now with numbers or a letter that would be fine but the grades are 4A, 4B and 4C, 5A....and so on.

4A is higher than 4B but excel thinks the opposite... so is there a way to change the way excel treats the second character.

hope you can follow that!!

Cheers thanks for any help.

fosteri01 (in a school)

[tuph]

You could try adding a helper column and reference that in the conditional formatting. For instance, if the grade column has 4A, 4B, 4C, you might have 49, 48, 47 in the helper column.

[Paul]

You might try this conditional format formula:

Please Login or Register  to view this content.

This assumes that grades starting with 5 are better than grades starting with a 4. It first checks to see if the leftmost digit in cell B1 is greater than the leftmost digit in A1. If so, the OR condition is met and the cell will be colored. If that part isn't true it then checks to see if both leftmost digits are equal and the rightmost digit in B1 is less than the rightmost digit of A1. (Since a is less than b is less than c.) If the second part is true, B1 will be colored, if not, it will stay colorless. You could obviously set other conditions to color the cell another color if it's less than, or equal to, A1, using a similar formatted formula.

PS - If 4 is actually a better grade than 5, simply change "=OR(LEFT(B1,1)>LEFT(A1,1)..." to "=OR(LEFT(B1,1)<LEFT(A1,1)..."

[fosteri01]

hey tuph & pjoaquin

Thanks guys - just tweaked what you gave me 4A is higher than 4B and 5 higher than 4. Took 10 minutes saved me loads of time and frustration and I've learnt something too!!

thanks again

Ian