Forum Discussion
How can I highlight cells that contain same text from any row in another worksheet?
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
=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)"
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)>0Select the desired formatting for the highlighting (e.g., a yellow fill), then select OK, and then Apply.
Hopefully that works.
- 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)"
