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

Copper 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

6 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)"

 

 

 

 

 

@OliverScheurich 

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.

@biagio99 

Yes go ahead and select a single cell, alternatively select "=SUM(select_range...)