+ Reply to Thread
Results 1 to 16 of 16

Conditional Formatting Based Upon Many Conditions

  1. #1
    Registered User
    Join Date
    03-15-2013
    Location
    Fond du Lac, WI
    MS-Off Ver
    Excel 2010
    Posts
    16

    Conditional Formatting Based Upon Many Conditions

    Hello everyone, i am new to this so give me a little break.

    What i am trying to do is use conditional formatting to make a cell a certain color based upon it being a number, that if the cell is older than today, and that another cell has an "X" in it. this is the formula i am using right now but i have 3 conditions.

    First =IF($E$59:$E$75<TODAY(),$B59:$B75<>"X") Turn Cell Red

    Second =IF(ISNUMBER($E59:$E75),$E59:$E75=TODAY()+7,$B59:$B75<>"X") Turn Cell Yellow When Within 7 Days Today.

    Third =IF(ISNUMBER($E59:$E75),$E59:$E75<=TODAY(),$B59:$B75="X") Turn Cell Green

    What im trying to accomplish is that when a cell with a date with in 7 days of today turns yellow as long as a different cell isnt "X". And i want it to turn green when a cell is at or behind the current date and has a "X" in it and turn red when a cell is at or behind the current date and dosent have an "X".

  2. #2
    Valued Forum Contributor Harribone's Avatar
    Join Date
    02-24-2013
    Location
    Midlands, UK
    MS-Off Ver
    Excel 2013/2016/365
    Posts
    528

    Re: Conditional Formatting Based Upon Many Conditions

    Change to =AND(1,2,3 etc)

    You can also use =OR(1,2,3 etc) if you wnat to meet just one criteria
    Say thanks, click *

  3. #3
    Registered User
    Join Date
    03-15-2013
    Location
    Fond du Lac, WI
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Conditional Formatting Based Upon Many Conditions

    I tried using the AND statement and when i did all of the cells just turned white, as if none of the conditions were applied.

  4. #4
    Valued Forum Contributor Harribone's Avatar
    Join Date
    02-24-2013
    Location
    Midlands, UK
    MS-Off Ver
    Excel 2013/2016/365
    Posts
    528

    Re: Conditional Formatting Based Upon Many Conditions

    Can you upload your sheet with example data for all conditions and non conditions met please. Will be able to sort problem easier for you.

  5. #5
    Registered User
    Join Date
    03-15-2013
    Location
    Fond du Lac, WI
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Conditional Formatting Based Upon Many Conditions

    sorry for the inconvience but how would i do that. like i said im not very advanced with forums..

  6. #6
    Valued Forum Contributor Harribone's Avatar
    Join Date
    02-24-2013
    Location
    Midlands, UK
    MS-Off Ver
    Excel 2013/2016/365
    Posts
    528

    Re: Conditional Formatting Based Upon Many Conditions

    Next to the "Post Quick Reply" button clikc "Go Advanced", from there scroll down to Manage Attachments. Upload you file and once done click Insert Inline.

  7. #7
    Forum Expert Kevin UK's Avatar
    Join Date
    12-07-2010
    Location
    Radstock, Somerset
    MS-Off Ver
    Excel 2003,2007,2010,2013
    Posts
    1,914

    Re: Conditional Formatting Based Upon Many Conditions

    Hi sdoern20

    How about the following rules!
    Select the cells where you want to apply the formatting.
    First rule: =AND($E59<TODAY(),$B59<>"x")
    Second Rule: =AND(ISNUMBER($E59),$E59-TODAY()=7,$B59<>"x")
    Third Rule: =AND(ISNUMBER($E59),$E59<TODAY(),B59="x")

    Apply the formatting.
    Regards Kevin


    Merged Cells (They are the work of the devil!!!)

  8. #8
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Conditional Formatting Based Upon Many Conditions

    cf should be in this format
    IF($E59<TODAY(),$B59<>"X") Turn Cell Red
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  9. #9
    Registered User
    Join Date
    03-15-2013
    Location
    Fond du Lac, WI
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Conditional Formatting Based Upon Many Conditions

    Quote Originally Posted by martindwilson View Post
    cf should be in this format
    IF($E59<TODAY(),$B59<>"X") Turn Cell Red
    So in conditional formatting your not supposed to use a range of cells?

  10. #10
    Registered User
    Join Date
    03-15-2013
    Location
    Fond du Lac, WI
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Conditional Formatting Based Upon Many Conditions

    Quote Originally Posted by martindwilson View Post
    cf should be in this format
    IF($E59<TODAY(),$B59<>"X") Turn Cell Red
    Also when you try this formula and you enter in a date that is less than today and also have an "X" in the other cell, it does nothing. It just leaves the cell white. This is the same problem i am having with my formula right now.

  11. #11
    Valued Forum Contributor Harribone's Avatar
    Join Date
    02-24-2013
    Location
    Midlands, UK
    MS-Off Ver
    Excel 2013/2016/365
    Posts
    528

    Re: Conditional Formatting Based Upon Many Conditions

    Kevin's answer will do this for you. don't use a range of cells, just apply to the first cell and copy down after it has been applied to one cell.

  12. #12
    Registered User
    Join Date
    03-15-2013
    Location
    Fond du Lac, WI
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Conditional Formatting Based Upon Many Conditions

    yupp kevins answer worked except i changed =7 to +7 so that it will stay yellow for an entire week. only problem is how do i apply it to more than one cell without having to type it in for each one. in your words copy down after it has been applied to one cell.

  13. #13
    Registered User
    Join Date
    03-15-2013
    Location
    Fond du Lac, WI
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Conditional Formatting Based Upon Many Conditions

    Quote Originally Posted by Kevin UK View Post
    Hi sdoern20

    How about the following rules!
    Select the cells where you want to apply the formatting.
    First rule: =AND($E59<TODAY(),$B59<>"x")
    Second Rule: =AND(ISNUMBER($E59),$E59-TODAY()=7,$B59<>"x")
    Third Rule: =AND(ISNUMBER($E59),$E59<TODAY(),B59="x")

    Apply the formatting.
    Kevin i understand rule 1 and rule 3 how they are working, would you be able to explain how rule 2 works with the -TODAY()=7 ?

  14. #14
    Registered User
    Join Date
    03-15-2013
    Location
    Fond du Lac, WI
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Conditional Formatting Based Upon Many Conditions

    The second rule i used to make the cell yellow =AND(ISNUMBER($E59),$E59-TODAY()=7,$B59<>"x") only works when the date is 7 days away. i need to have the cell stay highlighted for 7 days not just when its 7 days away. thanks guys

  15. #15
    Valued Forum Contributor Harribone's Avatar
    Join Date
    02-24-2013
    Location
    Midlands, UK
    MS-Off Ver
    Excel 2013/2016/365
    Posts
    528

    Re: Conditional Formatting Based Upon Many Conditions

    Firstly change the conditional formatting rule so that cell references are RELATIVE, i.e. $A$1 should just be A1. Any cells you arereferencing in the formula that you need to be the same for all conditional rules will need the $'s.

    Select the cell with this conditional formatting. Then click the paintbrush (format painter) under the Home menu. Then select the cell(s) to apply conditional formating to these. Or you call select the cell with con formatting, choose Conditional Formatting>Manage Rules and then edit the rule to apply to your range A1:A100 for example.

  16. #16
    Forum Expert Kevin UK's Avatar
    Join Date
    12-07-2010
    Location
    Radstock, Somerset
    MS-Off Ver
    Excel 2003,2007,2010,2013
    Posts
    1,914

    Re: Conditional Formatting Based Upon Many Conditions

    Hi sdoern20

    I thought it was for: =7 days, I misread your post.

    Second =IF(ISNUMBER($E59:$E75),$E59:$E75=TODAY()+7,$B59:$B75<>"X") Turn Cell Yellow When Within 7 Days Today.
    Just add the "<"

    =AND(ISNUMBER($E59),$E59-TODAY()<=7,$B59<>"X")

+ 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