Conditional Format when 2 dependant conditions met

[cornetazo]

Hi There,
I need a formula that will highlight a cell yellow when cell A1 is blank AND cell B2 matches or = 4 unique "text" entries.
<TABLE style="WIDTH: 163pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=217 border=0 x:str><COLGROUP><COL style="WIDTH: 91pt; mso-width-source: userset; mso-width-alt: 4425" width=121><COL style="WIDTH: 72pt; mso-width-source: userset; mso-width-alt: 3510" width=96><TBODY><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 91pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: #ffcc99" width=121 height=20>A</TD><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 72pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #ffcc99" width=96>B</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl70 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20> </TD><TD class=xl71 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver 0.5pt solid; BORDER-LEFT: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent"> Second</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl70 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20> 100</TD><TD class=xl71 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent"> First</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl70 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20> 101</TD><TD class=xl71 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent"> Second</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl70 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20> 102</TD><TD class=xl71 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent"> Third</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl70 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20> </TD><TD class=xl71 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent"> Sixth
</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl70 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20> 103</TD><TD class=xl71 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent"> Fourth</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl70 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20> 400</TD><TD class=xl71 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent"> Fifth</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl73 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20> 300</TD><TD class=xl71 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent"> Third</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl73 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20> </TD><TD class=xl71 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl73 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20> </TD><TD class=xl71 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent"> Sixth</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl73 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20> 250</TD><TD class=xl69 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent"> Sixth</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl73 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20> </TD><TD class=xl69 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent"> First</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl73 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20> </TD><TD class=xl69 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl74 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20></TD><TD class=xl75 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl74 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20></TD><TD class=xl76 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl74 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20></TD><TD class=xl75 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR></TBODY></TABLE>
Using the Example above, I would like all cells in Column A to highlighht yellow if, and only if, cell in Column A is blank AND meets the criteria in Column B of "First","Second","Third", or "Fourth".

 

[cornetazo]

One last request......

I have a similar issue but now need to conditionally format a cell if the date in cell C1 is greater than an adjacent date in cell D1 AND B1 is either "FirstSecondThirdFourth"

I currently have the following formula:
=AND(C1>D1),FIND(B1,"FirstSecondThirdFourth"))

 

[Aladin Akyurek]

One last request......

I have a similar issue but now need to conditionally format a cell if the date in cell C1 is greater than an adjacent date in cell D1 AND B1 is either "FirstSecondThirdFourth"

I currently have the following formula:
=AND(C1>D1),FIND(B1,"FirstSecondThirdFourth"))

Which cell do you want to CF?

 

[cornetazo]

Sorry.....

I need C1 to have the CF.

Thanks

 

[Aladin Akyurek]

Sorry.....

I need C1 to have the CF.

Thanks

Try...

=AND(ISNUMBER($C1),ISNUMBER($D1),$C1>$D1,$B1<>"",SEARCH($B1,"FirstSecondThirdFourth"))

 

[cornetazo]

Worked again!!!

Truly appreciate your help.

Thanks.......

 

[Aladin Akyurek]

Worked again!!!

Truly appreciate your help.

Thanks.......

You are welcome. Thanks for providing feedback.

 

[jasonb75]

Aladin,

Thinking about how CF deals with errors, how about

$C1>LARGE($C1:$D1,2)

in place of

ISNUMBER($C1),ISNUMBER($D1),$C1>$D1

 

[Aladin Akyurek]

Aladin,

Thinking about how CF deals with errors, how about

$C1>LARGE($C1:$D1,2)

in place of

ISNUMBER($C1),ISNUMBER($D1),$C1>$D1

A text value (say, a formula blank) in C1 would thwart CF. Likewise, we don't want D1 to be empty.

 

[jasonb75]

A text value (say, a formula blank) in C1 would thwart CF. Likewise, we don't want D1 to be empty.

In either of those cases, or any other where there is not a numeric value in each of the 2 cells, the LARGE() function, and sebsequently the logical test would return #NUM!

That, I believe has the same effect as a FALSE result in a CF formula, i.e. it's not TRUE so the format doesn't apply.

 

[Aladin Akyurek]

In either of those cases, or any other where there is not a numeric value in each of the 2 cells, the LARGE() function, and sebsequently the logical test would return #NUM!

That, I believe has the same effect as a FALSE result in a CF formula, i.e. it's not TRUE so the format doesn't apply.

I was explaining the choice I made was motivated by. Yes, CF treats error values as non-TRUE. If you are wanting/suggesting to replace the ISNUMBER($C1),ISNUMBER($D1),$C1>$D1 bit with $C1>LARGE($C1:$D1,2), by all means, that looks OK, although the hard-coded 2 is a tad undesirable.