Formatting: If the 1st or 2nd word in $A2 cannot be found anywhere in $B2, then fill entire row gray.

[jeffcoleky]

I need help translating this sentence into a conditional formatting formula:

"If either the first OR second word in cell $A1 CANNOT be found in cell $B1, then fill the entire row gray." I need this rule to apply to all rows in the sheet.

Can someone help please?

You can either download the sample data here: https://docs.google.com/open?id=0B_dQrAWFRl3VUVhrRk0zZjdxOVE

Or here it is pasted:

Excel 2010
	A	B	C	D	E	F
1	Owner	Parties	Address	City	State	Area
2	Milens Patsy & John	MILENS, PATSY, D; MILENS, PATSY; UNITED STATES OF CALIFORNIA ATTOR, NEY GENERAL; UNITED STATES OF CALIFORNIA US AT, TORNEY CA WESTERN; SMITH FINANCIAL; SANTA CLARA CARDSTON COUNTY ME, TRO DEPARTMENT OF IN, SPECTIONS PERMITS & SANTA CLARA & CARDSTON COUNTY, METROPOLITAN SEWER D, ISTRICT	100 Southern Parkway	Charlotte	NC	1012173 / CHURCH WEST
3	Jordan Smith Sara	DAVIES, JANET, CHARLENE; JORDAN SMITH, JANE, CHARLENE	123 Frankfort	Charlotte	NC	1033280 / NORTH
4	Noman Funding	WEIKELS, JOHN, Jr; WEIKEL, JOHN, J II; SANTA CLARA CARDSTON COUNTY ME, TRO DEPARTMENT OF CO, DES & REGULATIONS; SANTA CLARA CARDSTON COUNTY ME, TRO DEPARTMENT OF IN, SPECTIONS; CHASE BANK USA NA; PALUMBO PROPERTIES INC; WASHINGTON MUTUAL BANK FA; T; FREYS, RUTH, T; OM5 OF SANTA CLARA LTD; EDUCATIONAL TRAINING SYSTEMS I, NC; COMPUTER SCHOOL INC	3400 colby	Charlotte	NC	104152 / PARK PLEASANT
5	HUD	Jones, Melissa, F; Asset Acceptance Llc; National Credit Adjusters Llc; Dh Capital Management Inc	4040 Serene St	Mooresville	NC	10771140 / 3RD & MAIN
6	Johnsonton Jacob & Myrtle	JOHNSONTON, CHARLIE; JOHNSONTON, LINDA; COMMONWEALTH OF CALI WORKF, ORCE DEVELOPMENT CAB, INET	123 Smithie	Charlotte	NC	1083280 / MILL RD
7	Belker Tina and Perez Julio	BELKER, STEVEN; BELKER, TINA	1402 Rammers Rd	Mooresville	NC	200997 / LIBERTY
8	Smith Lauren & Bill	SMITH, RICHARD, F; SMITH, LAUREN, A; BELGAY AUTO SALES INC; CAPITAL ONE BANK USA NA; CAVALRY SPV I LLC; CONSECO SERVICES LLC; MIDLAND FUNDING LLC; COUNTY OF CARDSTON	100 1st St	Mooresville	NC	20111448 / CHINATOWN
9	Helenger Family Trust	HELENGER, JAMES, A; HELENGER, DEBORAH, T; MORTGAGE ELECTRONIC REGISTRATI, ON SYSTEMS INC; REPUBLIC BANK & TRUST CO; HELENGER, THOMAS, A; COMMONWEALTH OF CALI DEPAR, TMENT OF REVENUE	100 S Shelby	Charlotte	NC	20642 / CRITT/SMITH
10	Werle Jeff & Martha	WERLE, JEFF; WERLE, FEATHER; REPUBLIC BANK & TRUST CO; SANTA CLARA CARDSTON COUNTY ME, TRO DEPARTMENT OF IN, SPECTIONS PERMITS & COMMONWEALTH OF CALI FINAN, CE & ADMINISTRATION, CABINET; CA LIEN HOLDINGS LLC; CITIBANK NA; CITIBANK SOUTH DAKOTA NA	12515 Blackburn	Charlotte	NC	5024299 / SOUTH SCALINE
11	Smith Bob	Quets, Bob; SMITH Bank Na	1223 Vista Rd	Charlotte	NC	600 / COMMERCIAL
12	Christian Jessie	JESSIE, CHRISTIAN, M; JESSIE, MELISSA, T	125 W Main	Mooresville	NC	72 / COMMERCIAL
13	Ebert Sara	BANK OF CALIFORNIA NA; CA HOME LOANS SERVICING LP; COUNTRYWIDE HOME LOANS SERVICI, NG LP; EBERT, SARA	3000 Wellbrook Place	Charlotte	NC	900 / COMMERCIAL
Sheet1

 

[jeffcoleky]

That's exactly how i put it in. The formatting, for some reason, applies to every line using that formula. Here's a copy of the excel doc. I"m sure it's a simple fix but it just doesn't work for me.

giving up on me? If anyone wants to resurrect this post, please do.

 

[PaddyD]

Hi. Not been around much, hence belated reply. This still an issue? FYI - can't open the file you linked to