# 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!!  Register To Reply

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  Register To Reply

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)  Register To Reply

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!  Register To Reply

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.  Register To Reply

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  Register To Reply

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!  Register To Reply

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.  Register To Reply

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?  Register To Reply

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

Cells with CF usually do.  Register To Reply

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!!!!  Register To Reply

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  Register To Reply

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

You're welcome.  Register To Reply

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