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".

 

[Aladin Akyurek]

A1 and A12 should turn yellow, right?

 

[cornetazo]

This is correct!

 

[jasonb75]

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.

So that should be A1 and B1 then? With A1 and B2 you would have A1 and A5 yellow.

Maybe

=AND(ISBLANK(A1),NOT(ISBLANK(B1)),FIND(B1,"FirstSecondThirdFourth"))

 

[cornetazo]

The only cells that should be yello are A1 and A12 since they meet both conditions.

Let me try your formula and see what I get.

 

[cornetazo]

Formula did not work.

I tried the following:
=AND(ISBLANK(A1:A13),NOT(ISBLANK(B1:B13)),FIND(B1:B13,"FirstSecondThirdFourth")).

Are separators not needed for the text? Maybe I can enter the text in separate cells and reference them in the formula?

 

[jasonb75]

Use the formula exactly as I gave it to you, do not edit the ranges!

Do the blank cells contain formula?

 

[Aladin Akyurek]

This is correct!

Create a 4-cell range housing the 4 values of interest, that is, the set of First, Second, Third, and Fourth. Name this range List.

Select A1:A15.
Activate Conditional Formatting.
Run the following formula:

=AND($A1="",MATCH($B1,List,0))

Activate the Format button and apply the desired formatting.

Another formula option:

=AND($A1,$B1<>"",SEARCH($B1,"FirstSecondThirdFourth"))

 

[cornetazo]

Eureka!!!

Both formulas worked.

Much appreciated......

 

[Aladin Akyurek]

Eureka!!!

Both formulas worked.

Much appreciated......

Your are welcome. Thanks for providing us feedback.