+ Reply to Thread
Results 1 to 9 of 9

Conditional Formatting Not working correctly. Is my Formula incorrect??

  1. #1
    Registered User
    Join Date
    07-16-2019
    Location
    Cincy
    MS-Off Ver
    10
    Posts
    5

    Conditional Formatting Not working correctly. Is my Formula incorrect??

    Hello I am a new poster and have had a issue getting my conditional formatting to work correctly.
    What I am attempting to accomplish:
    Column/Row I2:AA2 to shade based on value in H2. Value 1-5 Green, 6-10 Yellow, 11-15 Orange, >=16 Red

    The formulas I used are:
    =IF($H2<=5,TRUE,FALSE) Applies to =$I$2:$AA$2
    =IF(AND($H2>=6,$H2<=10),TRUE,FALSE) Applies to =$I$2:$AA$2
    =IF(AND($H2>=11,$H2<=15),TRUE,FALSE) Applies to =$I$2:$AA$2
    =IF($H2>=16,TRUE,FALSE) Applies to =$I$2:$AA$2

    I have a value in H2 of 16 but the conditional formatting is not being applied??

  2. #2
    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
    80,647

    Re: Conditional Formatting Not working correctly. Is my Formula incorrect??

    CF formulae are not the same as normal formulae.

    Try this to get you started:

    =$H2<=5

    This is your formula for green.
    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.

  3. #3
    Registered User
    Join Date
    07-16-2019
    Location
    Cincy
    MS-Off Ver
    10
    Posts
    5

    Re: Conditional Formatting Not working correctly. Is my Formula incorrect??

    Thanks! What should it be for Orange 11 thru 15?

  4. #4
    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
    80,647

    Re: Conditional Formatting Not working correctly. Is my Formula incorrect??

    Try to work that out for yourself based on the changes I made to your first formula. What do you think it should be?

  5. #5
    Registered User
    Join Date
    07-16-2019
    Location
    Cincy
    MS-Off Ver
    10
    Posts
    5

    Re: Conditional Formatting Not working correctly. Is my Formula incorrect??

    From what I was aware there are 2 conditions for this which require the if,and formulas?
    My guess and its very odd because it works for a value of 11 was:
    =IF(AND($H2>=11,$H2<=15),TRUE,FALSE)

    You're saying CF formulas are different so now i am completely in the dark am I overthinking the difficulty?
    =$H2<=5 for Green
    =$H2>=6 for Yellow
    =$H2>=11 for Orange
    =$H2>=16 for Red

  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
    80,647

    Re: Conditional Formatting Not working correctly. Is my Formula incorrect??

    Those will work if you make sure they are in this order:

    =$H2<=5 for Green
    =$H2>=16 for Red
    =$H2>=11 for Orange
    =$H2>=6 for Yellow

    Can you work out why they won't work in the order you put them?

  7. #7
    Registered User
    Join Date
    07-16-2019
    Location
    Cincy
    MS-Off Ver
    10
    Posts
    5

    Re: Conditional Formatting Not working correctly. Is my Formula incorrect??

    Yes, because they will conflict with each other.

    The same reasoning I considered the IF(AND formulas originally.

    If this cannot be done what is the difference between the CF formulas and regular?

  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
    80,647

    Re: Conditional Formatting Not working correctly. Is my Formula incorrect??

    It can be done - what's the issue? If you get them in the correct order AND make sure that the Stop if True box is selected, they should work.

    If you wish to use an AND statement, then you will do it in the same way as I have shown you, i.e.:

    =AND($H2>5,$H2<11)

    The difference between CF and a regular formula is that the IF is implicit in the rule, so you don't need it. The rule will work for anything that is TRUE.

    Does this make sense?

  9. #9
    Registered User
    Join Date
    07-16-2019
    Location
    Cincy
    MS-Off Ver
    10
    Posts
    5

    Re: Conditional Formatting Not working correctly. Is my Formula incorrect??

    Absolutely! Thank you!

+ 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. Replies: 2
    Last Post: 03-14-2019, 03:52 PM
  2. Conditional Formatting with a Formula, it's highlighting incorrect cells
    By Hyflex in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-05-2019, 01:43 AM
  3. [SOLVED] Conditional Formatting not working properly - not highlighting cells correctly
    By Hedy in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-17-2018, 03:00 PM
  4. [SOLVED] Conditional Formatting not working correctly
    By patidallas22 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-16-2017, 04:00 PM
  5. [SOLVED] Incorrect conditional formatting
    By pderwael in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-24-2017, 05:39 AM
  6. Conditional Formatting not working correctly
    By Nitefox in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 5
    Last Post: 12-27-2013, 02:46 PM
  7. Incorrect Formula - conditional formatting
    By mahowarth in forum Excel General
    Replies: 4
    Last Post: 09-10-2013, 08:27 PM

Tags for this Thread

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