1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.
  2. Hi Guest, welcome to the TES Community!

    Connect with like-minded professionals and have your say on the issues that matter to you.

    Don't forget to look at the how to guide.

    Dismiss Notice

Help with conditional formatting in excel - geeks only!

Discussion in 'Personal' started by Ming_the_Merciless, Sep 14, 2011.

  1. Ming_the_Merciless

    Ming_the_Merciless New commenter

  2. harsh-but-fair

    harsh-but-fair Lead commenter

    Welcome to TES, ilovetravel.

    Do you see 'geek' as a term of endearment?
     
  3. I find it offensive.
     
  4. bizent

    bizent Lead commenter

    Hasn't it now become gleek?
     
  5. grandelf

    grandelf New commenter

    easy way is to just fill in the box with a colour,

    I use a simple traffic light style system, red below target, yellow on target, green above target
     
  6. Crowbob

    Crowbob Established commenter

    I am picturing your table as follows:
    Result 1 in cell A1, Result 2 in cell B1, Result 3 in C1 etc. For the purposes of the following example, your comparison grade is in cell F1. If you want to use A*, just enter a * in the cell.
    I assume you know how to do conditional formatting?
    Create 3 new rules as follows:
    1) For green: =CODE(A$1)<CODE($F$1)
    2) For yellow: =CODE(A$1)=CODE($F$1)
    3) For red: =CODE(A$1)>CODE($F$1)
    Basically you are converting the letters to their corresponding numeric keyboard map value. The letters are normally increasing values. So A is 65 and B is 66. You are then comparing the values.
     
  7. Ilovetravel

    Ilovetravel New commenter

    That's kind of you but I've been here a while...
    and yes I do [​IMG]

     
  8. Ilovetravel

    Ilovetravel New commenter

    Why?
     
  9. Ilovetravel

    Ilovetravel New commenter

    Hi Ming
    Thanks for this. I had seen it but is is based on numbers, I have letters. Any further ideas? ( without having to convert them all?)
    Cheers [​IMG]
     
  10. Ilovetravel

    Ilovetravel New commenter

    Thanks Crowbob - will have a go.
    I can only post with a delay which is rather frustrating!
     
  11. Only if you like 'Glee'...
     

Share This Page