If cell value in list in Sheet1 shows up in a cell value in Sheet2, highlight cell

[mkasledge1]

I have a spreadsheet with two sheets. Sheet 1 has a list of selected company names and Sheet 2 has a list of all company contacts along with the company name for each contact. I want to highlight the company name in Sheet 2 if that company name shows up in Sheet 1. There are 906 company names in Sheet 1 and 27,550 contacts in Sheet 2. If the company name next to the contact in Sheet 2 shows up in any of the 906 company names in Sheet 1 I want to highlight that cell a color. If it doesn't show up I don't want it highlighted. I need to do that for all 27,550 contacts (which contains company name) in Sheet 2. I tried COUNTIF but must be doing something wrong and would appreciate your help! Thanks in advance!

 

[Bernst]

Well, english is not my native language, therefor I keep it short and smart (hopefully )
Try MATCH with 3. parameter = 0

 

[etaf]

a countif() should work

for 2007, 2010 , 2013 , 2016 , 2019 or 365 Subscription excel version
Conditional Formatting

Highlight applicable range >>
Sheet2 range of company names - Change, reduce or extend the rows to meet your data range of rows

Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:
=countif( Sheet1range of company names , Sheet2:1st company name) >0

Format [Number, Font, Border, Fill]
choose the format you would like to apply when the condition is true
OK >> OK

here is an example
=COUNTIF(Sheet1!$A$2:$A$18,$A2)>0

Sheet2

Book4
	A
1	company name
2	A1
3	A3
4	A5
5	A7
6	A9
7	A11
8	B1
9	B4
10	B7
11	B10
12	B13
13	B16
14	B19
15	A1
16	A3
17	A5
18	A7
19	A9
20	A11
21	B1
22	B4
23	B7
24	B10
25	B13
26	B16
27	B19
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A27	Expression	=COUNTIF(Sheet1!$A$2:$A$18,$A2)>0	text	NO

Sheet1

Book4
	A
1	company name
2	A1
3	A2
4	A3
5	A4
6	A5
7	A6
8	A7
9	A8
10	A9
11	A10
12	A11
13	A12
14	A13
15	A14
16	A15
17	A16
18	A17
Sheet1

will only be on dropbox for a few days

Cond_Frmt-CompName - ETAF.xlsx

Shared with Dropbox

www.dropbox.com