CONDITIONAL FORMATTING PROBLEM - Microsoft Community

i want cell a1 either stay blank or change 1 of 3 colors when following true;

conditions true: b1>=1.5, c1 = "h" , d1 = "h".

a1=blank when b1,c1 & d1 false

a1=red when one true; b1 = true or  c1 = true or d1=true

a1=black when of two true; b1 & c1 = true or b1 & d1 = true or c1 & d1=true

a1 = green whenall tree true.

these formulas:

a1 = green; =and(b1>=1.5,c1="h",d1="sb") 

a1 = red; =or(b1>=1.5,c1="h", d1="sb")

a1 = black; =if((b1<1.5),and(c1="h",d1="sb")) 

a1 = black; =if(and(b1>=1.5,c1="h"),(d1<>"sb"))

my problem a1=red when b1 & d1 true , c1 false. a1 should black.

ncborn

when formula returns true evaluates 1 , can therefore trues can summed.

for example following formula returns 3     =sum(true,true,true)

if have "h" in cell d1 , entered following formula in other cell return true. note if statement not required.

=c1="h"        (this returns true)

therefore create sum formula each of conditions and the sum trues each condition , test if equals zero, 1, 2 or 3 trues.

=sum(b1>1.5,c1="h",d1="h")=0            sum of conditions is 0 indicates false (blank) (i assume no format required)

=sum(b1>1.5,c1="h",d1="h")=1            sum of conditions is 1 means one condition is true (red)

=sum(b1>1.5,c1="h",d1="h")=2            sum of conditions is 2 which means 2 conditions true (black)

=sum(b1>1.5,c1="h",d1="h")=3            sum of conditions is 3 which means 3 conditions true. (green)

Office / Excel / Windows other / Office 2010