Forum Discussion
Conditonal formatting - filter issue
Hi Ingeborg,
Thank you for your reply.
I have added an exeple Excel.
In the tab "tabel" I want the names to be highlighted in red if they are also in the colomn "names" in the tab "names".
And is there an ease way to copy/paste this condittional formatting to the entire colomn in tab "tabel"?
Thanks :)
There are several ways you can do this.
One is to create a helper column in the "tabel" sheet, using the formula (and copy down)
=match(A2,Names!A:A,0)
If a match for the name is found, the formula will return a number. You can now use conditional formatting to look at this helper column. Select A2 to A7 and create a rule that uses a formula and let the formula be
=isnumber($C2)
Then select a format.
Another option is to do the match in the conditional format formula. Select A2 to A7 and create a rule with a formula that goes like this
=isnumber(match($a2,Names!$A:$A,0))
and select a format.
You should avoid applying the conditional format to the whole column, because that can slow the spreadsheet down. In the Conditional Formatting Rules Manager you can set the range that the format should apply to.
If you are in Europe, your system may use the semicolon instead of the comma in formulas. I'll attach the workbook with the formulas in place.
Hi Ingeborg,
As cosmetic comment, ISNUMBER could be missed, just
=MATCH($A2,Names!$A:$A,0)
is enough for the rule formula
SergeiBaklan I know :smileyhappy: - I just wanted the logical progression from worksheet formula to all-in-one without making it too complex. Just using the same formula seemed more straightforward.
Oh, IngeborgHawighorst , I know what you know. That's only to provoke a bit people who read to ask themselves "Why so?"
