How can I highlight cells that contain same text from any row in another worksheet?

%3CLINGO-SUB%20id%3D%22%5C%26quot%3Blingo-sub-3144841%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3EHow%20can%20I%20highlight%20cells%20that%20contain%20same%20text%20from%20any%20row%20in%20another%20worksheet%3F%26lt%3B%5C%2Flingo-sub%26gt%3B%3CLINGO-BODY%20id%3D%22%5C%26quot%3Blingo-body-3144841%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3E%3CP%3EFor%20example%3A%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3EI%20have%20a%20column%20of%20company%20names%20in%20a%20NEW%20worksheet%20and%20relative%20info%20for%20each.%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3ENow%2C%20I%20have%20another%20column%20with%20company%20names%20in%20an%20OLDER%20worksheet.%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3EHow%20can%20I%20highlight%20the%20rows%20in%20the%20new%20worksheet%20that%20present%20same%20company%20names%20of%20the%20older%20worksheet%3F%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3EI%20was%20thinking%20about%20conditional%20formatting%20setting%20the%20%22specific%20text%22%20option%2C%20but%20how%20do%20I%20formulate%20it%3F%26nbsp%3B%20I%20need%20to%20highlight%20rows%20that%20contain%20same%20text%20of%20any%20row%20in%20a%20column%20from%20a%20different%20worksheet.%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3EMany%20thanks%26lt%3B%5C%2FP%26gt%3B%26lt%3B%5C%2Flingo-body%26gt%3B%3CLINGO-LABS%20id%3D%22%5C%26quot%3Blingo-labs-3144841%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3E%3CLINGO-LABEL%3EExcel%26lt%3B%5C%2Flingo-label%26gt%3B%3CLINGO-LABEL%3EFormulas%20and%20Functions%26lt%3B%5C%2Flingo-label%26gt%3B%26lt%3B%5C%2Flingo-labs%26gt%3B%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3C%2FLINGO-SUB%3E
New Contributor

For example:

 

I have a column of company names in a NEW worksheet and relative info for each.

 

Now, I have another column with company names in an OLDER worksheet.

 

How can I highlight the rows in the new worksheet that present same company names of the older worksheet?

 

I was thinking about conditional formatting setting the "specific text" option, but how do I formulate it?  I need to highlight rows that contain same text of any row in a column from a different worksheet.

 

Many thanks

5 Replies

@biagio99 

I agree with your instincts re: conditional formatting, though I think the better approach would be to use a formula rather that trying to use 'specific text'. You can do this by accessing New Formatting Rule, then selecting "Use a formula ..." as per the screen capture below.

 

Assuming that the new companies are in Sheet1, Column A (say, A1 to A10), and the old companies are in Sheet2, Column B (B1 to B20), select A1 on Sheet1, and drag it down to A10, then create a new rule with the following formula:

=MATCH(A1,Sheet2!$B$1:$B$20,0)>0

Select the desired formatting for the highlighting (e.g., a yellow fill), then select OK, and then Apply.

Hopefully that works.

KallunWillock_0-1644434107434.png

 

@biagio99 

=NOT(ISNA(MATCH(1,MMULT(--(ISNUMBER(SEARCH('old worksheet'!$F$2:$F$20,'new worksheet'!C2))),ROW($1:$1)^0),0)))

Maybe with this rule for conditional formatting if there isn't an exact match in the column of company names in the new worksheet.

Thank you guys, but I have tried both methods and I can't make it work apparently.

What I am trying to do is selecting the cells in the new sheet and:

conditional formatting-

highlight cells rules-

text that contains..-

Format cells that contain the text:-

 

and then I try to select the cells from the older sheet with the mouse, and after pasting that, it says "this type of reference cannot be used in a conditional formatting formula. change the reference to a single cell, or use the reference with a worksheet function, such as =SUM(A1:E5)"

 

 

 

 

 

@Quadruple_Pawn 

Thank you guys, but I have tried both methods and I can't make it work apparently.

What I am trying to do is selecting the cells in the new sheet and:

conditional formatting-

highlight cells rules-

text that contains..-

Format cells that contain the text:-



and then I try to select the cells from the older sheet with the mouse, and after pasting that, it says "this type of reference cannot be used in a conditional formatting formula. change the reference to a single cell, or use the reference with a worksheet function, such as =SUM(A1:E5)"

@biagio99 Hi. I'm not sure that  you have actually tried either method, because the approach that you've described is very different from the approach I described in my reply. If I understand you correctly, you have selected this option:

KallunWillock_0-1644452356627.png

Don't select that option. Select this option ("New Rule"):

KallunWillock_1-1644452409131.png

and then if you follow the process set out in my initial reply, it should then work. Please be sure to look at the screen captures I provided.