conditional formatting

[casey]

Thanks to Conan for answering my first similar question!
I now have a follow-up question.

Consider this data:

Col A Col B Col C Col D
Row 1 family_b bob family_b art
Row 2 jim family_b bob
Row 3 kirk family_d will
Row 4 greg family_e sam

1) If "bob" in Col B is found in Col D AND "family_b" ($A$1 a constant) is
NOT found in Col C, then "bob" in Col B turns blue.

OR

2) If "bob" in Col B is found in Col D AND "family_b" ($A$1 a constant) IS
found in Col C then "bob" in Col B turns red.

I can accomplish either #1 or #2 separately. But i can't seem to put both
together.

("bob" in Col B will turn red in above data. If I remove "family_b" from
Col C then "bob" in Col B would turn blue. $A$1 will change with manual
input to a different "family_x".)

Thanks,
casey

 

[Tom Hutchins]

You will need two 'Formula Is' conditions. For the RED condition (Condition
1), enter
=SUMPRODUCT(--($D$1:$D$4=B1),--($C$1:$C$4=$A$1))>0

Click the Add button to add another condition (Condition 2). For the BLUE
condition, enter
=SUMPRODUCT(--($D$1:$D$4=B1),--($C$1:$C$4<>$A$1))>0

Hope this helps,

Hutch

 

[casey]

Thanks, Hutch! Works perfectly!

You will need two 'Formula Is' conditions. For the RED condition (Condition
1), enter
=SUMPRODUCT(--($D$1:$D$4=B1),--($C$1:$C$4=$A$1))>0

Click the Add button to add another condition (Condition 2). For the BLUE
condition, enter
=SUMPRODUCT(--($D$1:$D$4=B1),--($C$1:$C$4<>$A$1))>0

Hope this helps,

Hutch