+ Reply to Thread
Results 1 to 4 of 4

Conditional Formatting

  1. #1
    Registered User
    Join Date
    06-19-2007
    Posts
    3

    Conditional Formatting

    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)

  2. #2
    Valued Forum Contributor
    Join Date
    02-09-2006
    Location
    Melbourne, Australia
    MS-Off Ver
    Office 2016
    Posts
    1,075
    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.
    Trish in Oz
    -------------
    A problem well defined is a puzzle half solved


    If you attach a sample of your workbook it will be easier to find a solution. Remember to remove/replace sensitive data before uploading the file. Look here if you need help with attachments:
    http://www.excelforum.com/faq.php?fa...b3_attachments

  3. #3
    Forum Moderator Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,824
    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)..."

  4. #4
    Registered User
    Join Date
    06-19-2007
    Posts
    3
    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1