+ Reply to Thread
Results 1 to 12 of 12

help with disobedient conditional formatting rule please

  1. #1
    Forum Contributor juntjoo's Avatar
    Join Date
    02-29-2008
    Location
    Cape Coral, FL
    MS-Off Ver
    Excel 2010 14.04.4760.1000
    Posts
    468

    help with disobedient conditional formatting rule please

    I have my current number("cur") and alternative number("alt") and I want the current cell color to reflect the the variance between the current and alternative number, green for being 5 less than the alternative and yellow for being equal and red for being 5 more, which should all be explained in the file. I use formulas to create this formatting and I put them outside the conditional formatting settings to make it easier to edit the formulas.

    Anyway, it's not working and IDK why. Might you? Thanks
    Attached Files Attached Files
    Excel 1.0.1 (16.0.14326.20140) (Android)
    Excel 2010 14.04.4760.1000

  2. #2
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: help with disobedient conditional formatting rule please

    Hello juntjoo,

    Please try it as shown on Sheet 2 of the attached sample Workbook.

    Regards.
    Attached Files Attached Files
    Please consider:

    Be polite. Thank those who have helped you. Then Click on the star icon in the lower left part of the contributor's post and add Reputation. Cleaning up when you're done. If you are satisfied with the help you have received, then Please do Mark your thread [SOLVED] .

  3. #3
    Forum Contributor juntjoo's Avatar
    Join Date
    02-29-2008
    Location
    Cape Coral, FL
    MS-Off Ver
    Excel 2010 14.04.4760.1000
    Posts
    468

    Re: help with disobedient conditional formatting rule please

    Quote Originally Posted by Winon View Post
    Hello juntjoo,

    Please try it as shown on Sheet 2 of the attached sample Workbook.

    Regards.
    Thanks, but it's doing the same thing, the opposite of what I want. The formulas are correct though no? Any other ideas?

  4. #4
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: help with disobedient conditional formatting rule please

    Hello juntjoo,

    Thank you for the feedback.

    Your Formulas are fine. No problem there.

    In your first Post you state:

    Anyway, it's not working and IDK why. Might you? Thanks
    It works on my side. Could you tell us a bit more of what is not working for you?

    Regards.

  5. #5
    Forum Contributor juntjoo's Avatar
    Join Date
    02-29-2008
    Location
    Cape Coral, FL
    MS-Off Ver
    Excel 2010 14.04.4760.1000
    Posts
    468

    Re: help with disobedient conditional formatting rule please

    Quote Originally Posted by Winon View Post
    Hello juntjoo,

    Thank you for the feedback.

    Your Formulas are fine. No problem there.

    In your first Post you state:



    It works on my side. Could you tell us a bit more of what is not working for you?

    Regards.
    Thanks. Here:https://www.dropbox.com/s/o7hupdottwdasop/condForm.xlsx?dl=0

    in case I need to edit it.

    The current value's cell color should reflect it's value's relationship to the alternative number, being more red towards values 5 more than the alternative, more green 5 LESS than the alternative number, as should be self explanatory, but as it shows with the correct formulas as we've mutually agreed, the color shown by the formulas is the opposite of what it should be. Or am I thinking backwards or something lol. I keep looking back at the file and if I'm not mistaken, values 17-22 for my target cell should be a shade of green/yellow no?

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: help with disobedient conditional formatting rule please

    Please post the workbook here using the forum's attachment facility.

    Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: help with disobedient conditional formatting rule please

    Sorry - just seen the workbook attached to the first post.

    You need to make the minimum reference in the CF rule G3 and colour it green, and the maximum E3 and colour it red. In other words, you have min and max the wrong way around.

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: help with disobedient conditional formatting rule please

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

  9. #9
    Forum Contributor juntjoo's Avatar
    Join Date
    02-29-2008
    Location
    Cape Coral, FL
    MS-Off Ver
    Excel 2010 14.04.4760.1000
    Posts
    468

    Re: help with disobedient conditional formatting rule please

    thank you. What's the difference? What I see is the formula box for maximum stating that (when) the value equals 5 less than alt number(17) the color should be green. What difference that it's under the maximum setting does that make? This has been confusing me forever.

    edit: oh it might have just hit me. "max" would be the max value reference for the range being alt#(22) + 5 (27) and red whereas the "min" would be alt# -5(17) green.... sorry. for some reason simple logic easily evades me. I'm also slow at getting jokes.
    Last edited by juntjoo; 07-12-2017 at 01:33 PM.

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: help with disobedient conditional formatting rule please

    Well, the clue is in the labels. Excel expects the minimum (lower) value to be in the field labelled minimum and the maximum (higher) value in the field labelled maximum. That's it - sorry, but I really can't see what's confusing about it. I spotted it as soon as I opened your file and looked at your CF settings.

  11. #11
    Forum Contributor juntjoo's Avatar
    Join Date
    02-29-2008
    Location
    Cape Coral, FL
    MS-Off Ver
    Excel 2010 14.04.4760.1000
    Posts
    468

    Re: help with disobedient conditional formatting rule please

    thank you all for helping

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: help with disobedient conditional formatting rule please

    You're very welcome. Glad it is now sorted.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Conditional formatting rule
    By sms1432 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-22-2017, 08:35 AM
  2. Conditional formatting - rule
    By pauldaddyadams in forum Excel General
    Replies: 1
    Last Post: 07-05-2016, 08:42 AM
  3. conditional formatting rule
    By pphg in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-21-2016, 09:20 AM
  4. conditional formatting rule
    By claralou in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-19-2016, 08:03 AM
  5. Conditional Formatting Rule if x is y and
    By nwpassage in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-29-2015, 12:18 PM
  6. Conditional formatting rule need help
    By Kagesen in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-25-2011, 04:42 AM
  7. Replies: 1
    Last Post: 07-29-2010, 11:26 AM

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