Conditional Formatting Question

[attv]

How to do this in Excel?

I have a list of certain company names in column G (Sheet 1)

And I have a list of all company names in column A (Sheet 2)

Which conditional formatting formula can I use to highlight cells in Sheet 1 if they match any names from Sheet 2?

 

[6StringJazzer]

Select column G sheet 1
Go to conditional formatting
Create new rule "using a formula"
For the formula:

=ISNUMBER(MATCH(G1,'Sheet 2'!$A:$A,0))

select desired formatting

 

[dave3009]

Book1
	A	B	C	D
1
2	Tesco
3	Amazon
4	John Lewis
5	Sports Direct
6	Microsoft
7
8
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:A7	Expression	=MATCH(A1,All_Companies,0)	text	NO

The named range All_Companies refers to Sheet2!A:A, in some older versions of Excel CF didn't like references to other sheets, M365 seems to handle it OK though.

If there's a match it'll return a number, which CF will evaluate to true and highlight the cells, if there is no match there will be an error, which CF will ignore.

 

[attv]

Not working sadly @6StringJazzer . All I get is

 

[Fluff]

Do you normally use commas or semi-colons as the separator in functions?

 

[attv]

Do you normally use commas or semi-colons as the separator in functions?

Commas

,

 

[attv]

Here is the worksheet with the table I have

Sheet

 

[dave3009]

test.xlsx
	D
1	company
54	StayLinked Corporation
58	1build
60	22Miles
61	22Miles
62	247PRO
1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D2:D3144	Expression	=MATCH(D2,COMPANY!A:A,0)	text	NO

I'm using M365, the suggestion I gave appears to work fine.

 

[Fluff]

Ok, how about

test.xlsx
	A	B	C	D	E	F
1	first_name	headline	current_position	company	previous_position_2	previous_company_2
52	PK	President & CEO CFO On The Go	President & CEO	On The Go	CFO & Director of Purchasing
53	Bob	--	President & Ceo
54	Gary	CEO and General Counsel StayLinked Corporation	President and CEO	StayLinked Corporation	Sr. V.P. and General Counsel V.P. Business Development
55	Ram	Easily create workflows & apps connecting people and information	President CEO		Apere CEO: Transitioning teams/customers to Citrix	Citrix
56	Marcie	Project Manager Trivium Consulting	Project Manager		Project Lead	CodeVA
57	Jolie	Surgical Robotics / Digital Health Program Manager at CITRIS and the Banatao Institute	Surgical Robotics / Digital Health Program Manager	CITRIS and the Banatao Institute	Programmatic Officer	University of California Berkeley
58	Dmitry	CEO/Founder at 1build | 🤖 + 👷‍♀️ = 🏘	CEO/Founder	1build	Lead Data Scientist	CloudKitchens
59	💌 Melissa	Making your inbox + the world a better place | Chief Meme Officer @ Gated | Host of 2 Pizza Marketing Podcast	Host	2 Pizza Marketing	Mobile Strategy Lead Travel	Google
60	Joey	CEO & Co-founder at 22Miles Inc.	CEO & Co-founder	22Miles
61	Tom	Chief Operating Officer	Chief Operating Officer	22Miles	Vice President of Global Business Development	22Miles
62	Cagri	Co-Founder @ 247PRO Software Engineer Graduate Student in AI	Co-Founder	247PRO	Software Consultant	Altis Teknoloji
63	George	Founder / CEO at 247PRO.COM Remodeling SaaS from estimating to project management	Founder / CEO	247PRO	Founder & CEO	Modobay Inc.
64	Nicole	CEO at 2NDNATURE		2NDNATURE
65	Nat	CTO at 2NDNATURE	Lead Software Engineer	2NDNATURE	GIS Analyst and WebGIS Developer	London Metropolitan University
66	Sushant	Chief Technology Officer / VP Engineering: Record of Leadership and Problem Solving while Building B2C/B2B Platforms and Product	Chief Technology Officer & Senior Vice President of Engineering	2x Connect	Tech Advisor	2x Connect
67	Manish	2xConnect - Solution to double productivity of B2B Demand and Lead Generation Service Providers	Founder	2x Connect	Senior Director / Director / Senior Manager Product Development	QuinStreet
68	John	Founder CEO 3DOS.io - President 3D Control Systems - 3DPrinterOS⭐️ 🇺🇸The Future of Manufacturing is One Platform - Decentralized	Founder - President - Chief Architect - Chairman of the Board	3DPrinterOS	Technology Innovation Incubator Mentor Coach	Tehnopol
69	Anton	Founder CTO | Serial Entrepreneur | Angel Investor ⭐️ 🇺🇸 Advanced Manufacturing Cyber Security Cloud Computing Blockchain AI	Technical Founder	3DPrinterOS	CTO co-founder
70	Leonardo E	CEO at 3DVES	CEO	3DVES	Administration Manager - Customer Service Operations	Seagate Technology
71	Victoria L.	Chief Executive Officer	Chief Executive Officer	3DVES	Director Of Business Development	3DVES
72	Peter D.	CEO at 42 Technologies	CEO	42	VISA Olympics VIP Hospitality Program Canada Olympic House	Pyeongchang Organizing Committee for the 2018 Olympic & Paralympic Winter Games
73	Nick	CTO @ 42 Technologies | Forbes 30 Under 30	Founder & CTO	42	Software Engineer	Benbria
74	Venky	CEO and Founder at 4CRisk.ai	CEO and Founder	4CRisk.ai	V.P of Professional Services	MetricStream
75	Supra	Co-Founder and COO at 4CRisk.ai Inc.	Co-Founder and COO	4CRisk.ai	Director of GRC Solutions	MetricStream
76	Patrick	CTO at 4CRisk.ai	CTO	4CRisk.ai	Software Architect	Angi
1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D2:D3144	Expression	=ISNUMBER(MATCH(D2,COMPANY!$A:$A,0))	text	NO
C2:C3144	Cell Value	contains "chief operating officer"	text	NO
C2:C3144	Cell Value	contains "c.e.o."	text	NO
C2:C3144	Cell Value	contains "chief executive officer"	text	NO
C2:C3144	Cell Value	contains "chief technology officer"	text	NO
C2:C3144	Cell Value	contains "cto"	text	NO
C2:C3144	Cell Value	contains "coo"	text	NO
C2:C3144	Cell Value	contains "founder"	text	NO
C2:C3144	Cell Value	contains "ceo"	text	NO

 

[attv]

I don't know what's up @dave3009 & @Fluff

Tried to paste the same formula in "Use a formula to determine which cells to format" after highlighting the whole D column, like in the picture, and still got this error "there's a problem with this formula"