Results 1 to 14 of 14
  1. #1
    New Lounger
    Join Date
    May 2015
    Posts
    4
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Conditional formatting if cell value is < or > value in another cell

    Excel 2010

    I am trying to format C37 to highlight if the value in C37 is < or > the value in D37.
    Then, I need to copy this down to C66 (if the value in C38 is < or > D38 if the value in C39 is < or > D39 etc.).

    I have tried many variations, but I cannot get it to work properly.

    Thank you for your help.

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts
    Hi

    Welcome to the Lounge!

    When you set up the rule for [c37], remove the default dollar signs from the D37 reference.
    This will make it a 'relative' formula, which will then automatically adjust when you copy it down.

    zeddy

  3. #3
    New Lounger
    Join Date
    May 2015
    Posts
    4
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thank you, Zeddy.

    That is one of the variations I tried. I did remove the dollar sign ($), but the row number did not advance when I copied "formatting only". I did a full copy down (all cells highlighted) and one cell at a time, but the row number did not advance.

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts
    Hi

    When setting up the rule for [c37] you need to remove all dollar signs from c37 as well.
    see attached sample file.

    zeddy
    Attached Files Attached Files

  5. The Following User Says Thank You to zeddy For This Useful Post:

    LL99 (2015-05-26)

  6. #5
    New Lounger
    Join Date
    May 2015
    Posts
    4
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Thumbs up

    Thank you, I don't understand why it was not copying properly for me, but it has worked!

  7. #6
    5 Star Lounger Lugh's Avatar
    Join Date
    Jun 2010
    Location
    Indy
    Posts
    620
    Thanks
    166
    Thanked 77 Times in 68 Posts
    Quote Originally Posted by LL99 View Post
    I did remove the dollar sign ($), but the row number did not advance when I copied "formatting only".
    Quote Originally Posted by LL99 View Post
    I don't understand why it was not copying properly for me
    Sounds like you may be confusing "Conditional formatting" and the default formatting which every cell has--usually "General" iirc unless you change it.

    Copying "formatting only" will not copy your formula, that's likely why it didn't work as you expected. You need to do a normal copy, which includes values, formulae, and formatting.

    "Conditional formatting" is totally different from the above.

  8. #7
    New Lounger
    Join Date
    May 2015
    Posts
    4
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thank you, Mike.

    But, if I do a "normal copy" I will lose the value in that cell. Usually, if I paste formatting only, it will paste the conditional formatting and the formulas contained in the conditional formatting.

  9. #8
    New Lounger
    Join Date
    Jun 2015
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Conditional formatting ??

    Quote Originally Posted by LL99 View Post
    Thank you, I don't understand why it was not copying properly for me, but it has worked!
    Could you possibly post an example of the code to do this? I would like to highlight in someway a cell if it is < or > another cell. Possible change background color of cell. I have no idea where to start. Version 2003 Office/Excel -- Thanks
    Last edited by wayne@high-tec.com; 2015-06-11 at 17:35.

  10. #9
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 649 Times in 592 Posts
    Wayne,

    You did not specify what version of Excel you are using but here are instructions for 2010:

    1. Select cell you want to place the conditional formatting

    2. Home Tab > Conditional formatting > New Rule...

    wayne1.png

    3. Set up the Edit Formatting Rule dialogue box as follows and replace =$B$17 with the cell that you want to compare the selected cell to.

    Wayne2.png

    4. Click OK > Apply

    HTH,
    Maud

  11. #10
    New Lounger
    Join Date
    Jun 2015
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    conditional formatting

    Quote Originally Posted by Maudibe View Post
    Wayne,

    You did not specify what version of Excel you are using but here are instructions for 2010:

    1. Select cell you want to place the conditional formatting

    2. Home Tab > Conditional formatting > New Rule...

    wayne1.png

    3. Set up the Edit Formatting Rule dialogue box as follows and replace =$B$17 with the cell that you want to compare the selected cell to.

    Wayne2.png

    4. Click OK > Apply

    HTH,
    Maud

    I thought I stated that I was using Office/Excel 2003. Sorry bout that. I haven't tried your solution yet but I very much appreciate your reply and help. I'll let you know if it works on Excel 2003.

  12. #11
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts
    Hi Wayne

    I have attached an Excel2003 version of my post#4 sample file.

    zeddy
    Attached Files Attached Files

  13. #12
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 649 Times in 592 Posts
    Duh...Wayne you certainly did. It was right in front of my back!

  14. #13
    New Lounger
    Join Date
    Jun 2015
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Maudibe View Post
    Duh...Wayne you certainly did. It was right in front of my back!
    I hate it when that happens. The edumocation you gave me about the conditional formatting worked just as you said on the Excel 2003 version as well. Your help is much appreciated.

  15. #14
    New Lounger
    Join Date
    Jun 2015
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by zeddy View Post
    Hi Wayne

    I have attached an Excel2003 version of my post#4 sample file.

    zeddy
    Thank you Zeddy. I have used Excel for years and never looked into the Conditional Formatting under the FORMAT tab. My Dad always said the day wasn't complete unless you learned something. Well with the help from you guys I can rest well tonight.

Posting Permissions

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