+ Reply to Thread
Results 1 to 6 of 6

Conditional Format between two values based on another value

  1. #1
    Registered User
    Join Date
    09-08-2009
    Location
    Hawaii
    MS-Off Ver
    Excel 2013
    Posts
    68

    Conditional Format between two values based on another value

    This is so easy but I'm clouded right now. I have a value in cell B2 (949). I have two columns of numbers that follow a sequential pattern (numbers get larger, not smaller or mixed). I would like to conditional format the row of numbers in the two columns based on the number in cell B2. So right now the value is 949, I would like row 13 to format because those values are 912 and 1025. My 949 value falls in between them.

    I tried using =AND($B$2>=A5,$B$2<=B5) but it's not working properly.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Conditional Format between two values based on another value

    Please Login or Register  to view this content.
    works but doesnt work if it equals exactly the number in b2

    *edit*
    OK does work....numbers are not whole numbers so in essence it is correct
    Last edited by humdingaling; 04-19-2013 at 03:53 AM.

  3. #3
    Registered User
    Join Date
    09-08-2009
    Location
    Hawaii
    MS-Off Ver
    Excel 2013
    Posts
    68

    Re: Conditional Format between two values based on another value

    Quote Originally Posted by humdingaling View Post
    Please Login or Register  to view this content.
    works but doesnt work if it equals exactly the number in b2
    Thanks, I had my referencing off. And it is weird it doesn't work if the number is exact. I can work with this for now. Thanks for the help. I'll leave the thread open if anybody else knows the answer.

  4. #4
    Valued Forum Contributor Lemice's Avatar
    Join Date
    04-13-2013
    Location
    Somewhere.
    MS-Off Ver
    Excel 2016
    Posts
    696

    Re: Conditional Format between two values based on another value

    Hello,

    Weird, as humdingaling said, it works, and it does for me too
    See the sample file attached
    TempCF.xlsx
    (copy pasta from Ford)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools

    Regards,
    Lem

  5. #5
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Conditional Format between two values based on another value

    Perhaps

    =AND($B$2>=$A5;$B$2<=$B5)


    Edit: Too late reply but just to confirm that this works for me too as Lem sample...
    Last edited by Fotis1991; 04-19-2013 at 03:56 AM. Reason: Edit
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  6. #6
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Conditional Format between two values based on another value

    it doesnt work on some of them due to the rounding

    342.00

    - 113.92
    114.92 227.83
    228.83 341.75
    342.75 455.67
    456.67 569.58
    570.58 683.50
    684.50 797.42
    798.42 911.33
    912.33 1,025.25
    1,026.25 1,139.17
    1,140.17 1,253.08
    1,254.08 1,367.00

    342 doesn't highlight anything because it is greater than 341.75 but lower than 342.75
    so it is working as intended
    Last edited by humdingaling; 04-19-2013 at 04:00 AM.

+ 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