# Using if and And together in conditional formatting multiple rules!

1. ## Using if and And together in conditional formatting multiple rules!

Hi

I am not particularlly great with complex formula but I'll give it go!
I have three rules I am trying to get into conditional formatting for my spreadhseet which are:
1. If(b29<10% of b24) = equal fill box red
2. If(b29>=10% of b24)and(b29+c29< b24) =equals fill box red
3. If(b29>=10% of b24)and(b29+c29>=b24) =equals fill box green

I am hoping someone can help me!!

2. ## Re: Using if and And together in conditional formatting multiple rules!

Hello
For the first two rules as you need Red color so you can combine both the rules
so the formula would be
=OR(B29<(0.1*B24),AND(B29>=(0.1*B24),(B29+C29)< B24))

for the third rule use this one
=AND(B29>=(0.1*B24),(B29+C29)>=B24)

Tell me if it works right….

Hope it helps
Do ask for any other query you may have…

If you are satisfied then mark then PLEASE mark this thread as “SOLVED” (by going to thread tools at the top and clicking on “mark this thread as solved”) and you can just click on ADD REPUTATION below my post to say thanks...
Regards
Sourabh Gupta

3. ## Re: Using if and And together in conditional formatting multiple rules!

May be:

=B29<B24*0.1

=AND(B29>=B24*0.1,B29+C29<B24)

=AND(B29>=B24*0.1,B29+C29>=B24)

4. ## Re: Using if and And together in conditional formatting multiple rules!

Thank you, it is accepting the formula (which I couldn't get it do) but its not applying the formatting!

5. ## Re: Using if and And together in conditional formatting multiple rules!

Mine and Sourabh's returns TRUE/FALSE and can be accepted to be CF criteria!

If it doe not work, try topost a small dummy workbook.

6. ## Re: Using if and And together in conditional formatting multiple rules!

Remember while doing conditional formatting that you select the correct range....and maybe try these formula
=OR(\$B\$29<(0.1*\$B\$24),AND(\$B\$29>=(0.1*\$B\$24),(\$B\$29+\$C\$29)< \$B\$24))
and
=AND(\$B\$29>=(0.1*\$B\$24),(\$B\$29+\$C\$29)>=\$B\$24)
this would fix the criteria cells ..B24 and B29 would be used as a criteria for all cells now....

Tell me if it works right….

Hope it helps
Do ask for any other query you may have…

If you are satisfied then mark then PLEASE mark this thread as “SOLVED” (by going to thread tools at the top and clicking on “mark this thread as solved”) and you can just click on ADD REPUTATION below my post to say thanks...
Regards
Sourabh Gupta

7. ## Re: Using if and And together in conditional formatting multiple rules!

Thanks guys like i said don't have the best skills!!! ok so heres a dummy version of my spread sheet
dummy.xls

Hope thats attached ok!

8. ## Re: Using if and And together in conditional formatting multiple rules!

Seems to me that you only need one CF. Statically format the cell as red, then

=(b29 >= 0.1 * b24) * (b29+c29 >= b24)

... and format as green.

9. ## Re: Using if and And together in conditional formatting multiple rules!

The cell that I am looking at has a formula in it iteslf, would that effect the conditional formatting?

10. ## Re: Using if and And together in conditional formatting multiple rules!

Cells with CF usually do.

11. ## Re: Using if and And together in conditional formatting multiple rules!

thats what i thought, the conditional formula works if i do it on the cell below it works perfect!!!!

12. ## Re: Using if and And together in conditional formatting multiple rules!

Originally Posted by shg
Seems to me that you only need one CF. Statically format the cell as red, then

=(b29 >= 0.1 * b24) * (b29+c29 >= b24)

... and format as green.
Thats done it! Thanks you so much

13. ## Re: Using if and And together in conditional formatting multiple rules!

You're welcome.

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