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?

 

[Fluff]

Try changing the commas to semi-colons.

 

[dave3009]

I mentioned that some previous versions of Excel didn't handle other sheet references well, did you try defining a name for the list on the COMPANY tab then replace COMPANY!A:A with that name?

It is just a case of highlighting all the company names in the COMPANY tab and entering a name in the box shown in the image

The formula:
=MATCH(D2,TheNameYouMade,0)

 

[Fluff]

I mentioned that some previous versions of Excel didn't handle other sheet references well

That changed in 2010 so should not be a problem for the OP.

 

[attv]

Try changing the commas to semi-colons.

Actually this worked. Changed 2 commas to semicolons and formatting was all right.

Thank you all for help

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[attv]

Hey @Fluff - 1 additional question here popped up today, can you help?

I want the formula to do the same but not be super strict

So when column G (Sheet 1) would say something like "Random Company" and column A (Sheet 2) has on list "Random Company AI" I still want the formula to catch it and format cell in column G (Sheet 1).