Results 1 to 7 of 7
Like Tree1Likes
  • 1 Post By Scott Huish

Conditional Formatting and an IF statement

This is a discussion on Conditional Formatting and an IF statement within the Excel Questions forums, part of the Question Forums category; I need to know if it's possible to make Excel use an IF statement to decide whether to apply conditional ...

  1. #1
    New Member
    Join Date
    May 2007
    Posts
    41

    Default Conditional Formatting and an IF statement

    I need to know if it's possible to make Excel use an IF statement to decide whether to apply conditional formatting to a cell. What I have is this:

    If cell G2 is greater than or equal to .15 then cell D2 should have no conditional formatting applied to it, cell should have no fill.

    If cell G2 is less than .15 then the following conditional formatting should be applied:
    Condition 1: If cell D2 value is greater than or equal to 00:06:00 cell fill color should be green
    Condition 2: If cell D2 value is less than 00:06:00 cell fill color should be red

  2. #2
    MrExcel MVP
    Moderator
    Scott Huish's Avatar
    Join Date
    Mar 2004
    Location
    Oregon
    Posts
    17,080

    Default Re: Conditional Formatting and an IF statement

    What you are asking is possible, but you don't need an IF statement to do it, you just need 2 conditional formatting formulas:

    Condition 1, Formula Is:
    =AND($G2>=0.15,$D2>="06:00"+0)
    Format with green fill

    Condition 2, Formula Is:
    =$G2>=0.15
    Format with red fill.
    coheed1785 likes this.
    Office 2007/2010

  3. #3
    New Member
    Join Date
    May 2007
    Posts
    41

    Default Re: Conditional Formatting and an IF statement

    Thank you, I hadn't thought of using a formula. We ended up making it work by using 3 different conditions:

    =G2>=0.15
    Formatting: none

    =(D2>$D$22)
    formatting: green fill

    =(D2<$D$22)
    Formatting: red fill

    D22 has the goal of 00:06:00 listed in it, for some reason we couldn't get the formula in the conditional fomatting to work with a time in it, but it worked with a cell reference to that amount.

  4. #4
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    32,547

    Default Re: Conditional Formatting and an IF statement

    =(D2<$D$22)
    Formatting: red fill

    D22 has the goal of 00:06:00 listed in it, for some reason we couldn't get the formula in the conditional fomatting to work with a time in it, but it worked with a cell reference to that amount.
    To make it work with the time in the formula, try like this

    =(D2>"00:06:00"+0)

    or

    =(D2>TIME(0,6,0))
    Want better/faster responses to your questions?
    Use Excel Jeanie to post samples of your sheet.

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  5. #5
    New Member
    Join Date
    May 2007
    Posts
    41

    Default Re: Conditional Formatting and an IF statement

    Making this conditional formatting made me think of something I'd like to try in a different file, it's similar to what we did in this one but just different enough that I need a little help with the formula. What I'd like to do is put conditional formatting on cell C2 that looks to see if cell D2 is blank or not. If cell D2 is blank, cell C2 should have no formatting. If cell D2 is nonblank and cell C2 is blank, cell C2 should be filled yellow. If both cells are nonblank cell C2 should have no formatting. The information in cell D2 will be an eight digit number, so I'm pretty sure my first condition can be something like:

    Condition 1 Formula is:
    =D2>1
    formatting: none

    What I need help with is the formula for the second and third conditions, would I use an IF statement, Counta statement, or an And statement?

  6. #6
    MrExcel MVP
    Moderator
    Scott Huish's Avatar
    Join Date
    Mar 2004
    Location
    Oregon
    Posts
    17,080

    Default Re: Conditional Formatting and an IF statement

    You don't need conditions for no formatting, you only need conditions for what you want to format. Conditional Formatting only applies for something that can be evaluated as TRUE.

    Your formula could be:

    =AND($C2="",$D2<>"")
    Office 2007/2010

  7. #7
    New Member
    Join Date
    Jan 2014
    Posts
    1

    Default Re: Conditional Formatting and an IF statement

    Quote Originally Posted by honeybee129 View Post
    Thank you, I hadn't thought of using a formula. We ended up making it work by using 3 different conditions:

    =G2>=0.15
    Formatting: none

    =(D2>$D$22)
    formatting: green fill

    =(D2<$D$22)
    Formatting: red fill

    D22 has the goal of 00:06:00 listed in it, for some reason we couldn't get the formula in the conditional fomatting to work with a time in it, but it worked with a cell reference to that amount.
    The reason why reference method is working: when you use reference in a formula, excel calulates the numerical value of referred cell contents . So if the reference contain time format, excel in background converts it to value.

    Say for exapmle
    =value(06:00:29) is 0.250336
    and =
    value(06:00:30) is 0.250347.

    Obviously 0.25347 is > 0.250336. So your formula comes True/False and CF aplies accordingly. If you write time content directly without using "+0" it should not work because of format compatibility issues in formula execution.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com