Forum Discussion
How can I highlight cells that contain same text from any row in another worksheet?
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)"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.
