Conditional formatting with multiple criteria

[chika235]

Hi gurus,

Just trying to setup several conditional formatting to achieve the below:

Column A & Column B are targeted range.

Ultimately what I want is:

If both columns = 0 or Blank, or A=B, then no fill
If Column A = 0 or "", and Column B <> 0 or "", then green
If Column B = 0 or "", and Column A <> 0 or "", then red
If Column A <> 0 or "", and Column B <>0 or "", and Column A <> Column B, then orange

I put through the below formula:

=OR(AND(A1=0,B1=0),AND(ISBLANK(A1),ISBLANK(B1)),A1=B1) -> No fill
=AND(OR(A2=0,ISBLANK(A2)),OR(B2<>0,ISBLANK(B2)=FALSE)) -> Green
=AND(OR(B3=0,ISBLANK(B3)),OR(A3<>0,ISBLANK(A3)=FALSE)) -> Red
=AND(OR(A4<>0,ISBLANK(A4)=FALSE),OR(B4<>0,ISBLANK(B4)=FALSE),A4<>B4) -> Orange

My rules stopped at Green and don't perform last 2 conditions.

What have I done wrong here?

Thanks.

[sandy666]

try
=OR(AND($A1=0,$B1=0),AND(ISBLANK($A1),ISBLANK($B1)),$A1=$B1) - no color
=AND(OR($A1=0,ISBLANK($A1)),OR($B1<>0,ISBLANK($B1)=FALSE)) - green
=AND(OR($B1=0,ISBLANK($B1)),OR($A1<>0,ISBLANK($A1)=FALSE)) - red
=AND(OR($A1<>0,ISBLANK($A1)=FALSE),OR($B1<>0,ISBLANK($B1)=FALSE),$A1<>$B1) - orange

order of rules with no color checked like above

[chika235]

try
=OR(AND($A1=0,$B1=0),AND(ISBLANK($A1),ISBLANK($B1)),$A1=$B1) - no color
=AND(OR($A1=0,ISBLANK($A1)),OR($B1<>0,ISBLANK($B1)=FALSE)) - green
=AND(OR($B1=0,ISBLANK($B1)),OR($A1<>0,ISBLANK($A1)=FALSE)) - red
=AND(OR($A1<>0,ISBLANK($A1)=FALSE),OR($B1<>0,ISBLANK($B1)=FALSE),$A1<>$B1) - orange

order of rules with no color checked like above

It workeddddd!!! Thank you so much

[sandy666]

You are welcome

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.