# 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. ## 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. ## 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.

3. ## 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. ## 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))

5. ## 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. ## 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.

=AND(\$C2="",\$D2<>"")

7. ## Re: Conditional Formatting and an IF statement

Originally Posted by honeybee129
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.

8. ## Re: Conditional Formatting and an IF statement

I would like to create a conditional formatting rule for the following:

Cell C2: If cell C1=36 then the text in C2 would be 3x/8 weeks, 2x/6 weeks (36)

Once that is completed, I'd like to create additional conditional formatting rules for the same cell.

Is that possible?

9. ## Re: Conditional Formatting and an IF statement

IF(AND(AE2="YES",E2>TODAY()),1,IF(AND(AE2="YES";F2
Trying this, but it does not work in conditional formatting, obviously, but this is what I'm trying to do.

When AE2 is Yes and the date in E2 is greater than todays, the cell should be red.

When AE2 is Yes and the date in F2 is smaller than todays, the cell should also be red.

I have edited several times now, trying to show the whole
IF statement, but it is cut short in the post

10. ## Re: Conditional Formatting and an IF statement

Drop the IF, you need something that generates a TRUE/FALSE value:

Try this:
=AND(AE2="Yes",OR(E2>TODAY(),F2<TODAY()))

Page 1 of 2 12 Last

#### Posting Permissions

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