Results 1 to 10 of 10
  1. #1
    5 Star Lounger
    Join Date
    Feb 2008
    Posts
    1,007
    Thanks
    63
    Thanked 2 Times in 2 Posts

    Highlighting Values using conditional formatting

    I have data in Col K & L. Where the values in Col K < than the value in Col L, then the cell/s in Col K that is less than the value in Col L in the same row as Col K must be highlighted in Blue

    I have tried to do this using conditional formatting but cannot get it to work. I have set up a separate sheet (desired result) to show you what it should look like if conditional formatting was applied

    Your assistance in resolving this will be most appreciated
    Attached Files Attached Files

  2. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Star Lounger AlanWade's Avatar
    Join Date
    Dec 2009
    Location
    Sweden
    Posts
    77
    Thanks
    9
    Thanked 1 Time in 1 Post
    Assuming I am following you right look at the attached file and try changing a few values, is this what you want to do?
    If it is then a very simple way to do it is to highlight the cell that you want to change the background of if the value is lower.
    Click on Conditional formatting then on Highlight Cell Rules.
    From here you have a number of options, choose Less Than.
    In the box that opens type in your reference cell, for example =L5 and change the condition in the right hand box to whatever colour you want it to be.
    Do this for each of the cell references you want to highlight.
    Attached Files Attached Files

  4. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    La Jolla,CA
    Posts
    1,072
    Thanks
    12
    Thanked 36 Times in 35 Posts
    You're trying to use a < comparison with cells that contain text and that doesn't compute.
    Put the text in separate columns and then use the < comparison.

  5. #4
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Manchester, United Kingdom
    Posts
    113
    Thanks
    8
    Thanked 15 Times in 14 Posts
    Partial answer. The problem is maybe that your values are text but you want a numeric comparison "11.37" is alphabetically less than "7.60" but numerically greater. I tried the following:

    Enter the data as numbers and apply a number format '0.00 "Times"' (double quotes are part of the format) so the display is similar to your example. Then apply your conditional format which should work as expected. (11.37 is not less than 7.60 and is not highlighted)

    Partial answer because you might not be able to separate the numbers from the text, and I don't know why some are "times" and some are "days" and if it matters

    Ian

  6. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,224
    Thanks
    14
    Thanked 340 Times in 333 Posts
    You are comparing text to text and a "1" < "7" so you get the expected results. You can convert all the cells to numbers, or use something like this for your conditional format:

    Code:
    =VALUE(LEFT(K5,FIND(" ",K5)-1))<=VALUE(LEFT(L5,FIND(" ",L5)-1))
    This compares the number to the other number by extracting the text before the space and converting it to a value.

    Steve

  7. #6
    5 Star Lounger
    Join Date
    Feb 2008
    Posts
    1,007
    Thanks
    63
    Thanked 2 Times in 2 Posts
    Hi Guys

    Thanks for all your input much appreciated.


    Howard

  8. #7
    5 Star Lounger
    Join Date
    Feb 2008
    Posts
    1,007
    Thanks
    63
    Thanked 2 Times in 2 Posts
    Hi Kweaver

    Thanks for the advise

    Howard

  9. #8
    5 Star Lounger
    Join Date
    Feb 2008
    Posts
    1,007
    Thanks
    63
    Thanked 2 Times in 2 Posts
    Hi Ian

    Thanks for the advise. I now realize where the problem lay. Your solution worked perfectly

    Howard

  10. #9
    5 Star Lounger
    Join Date
    Feb 2008
    Posts
    1,007
    Thanks
    63
    Thanked 2 Times in 2 Posts
    Hi Steve

    Thanks for the advise and input. I tried your solution and it worked spot on

    Howard

  11. #10
    5 Star Lounger
    Join Date
    Feb 2008
    Posts
    1,007
    Thanks
    63
    Thanked 2 Times in 2 Posts
    Hi Alan

    Thanks for input & help, much appreciated

    Howard

Posting Permissions

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