Feb 09 2022 10:32 AM
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
Feb 09 2022 11:19 AM
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.
Feb 09 2022 11:24 AM
=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.
Feb 09 2022 02:25 PM
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)"
Feb 09 2022 02:25 PM
Feb 09 2022 04:21 PM
@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:
Don't select that option. Select this option ("New Rule"):
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.
Feb 16 2023 12:37 AM
Yes go ahead and select a single cell, alternatively select "=SUM(select_range...)