01-27-2019 01:53 PM
01-27-2019 01:53 PM
I have been adding condional formatting to my Excel Document.
in one tab I have a colom with invoice due dates, and in another tab I keep track of the payments.
So when the payment = 0,00, the referring invoice due date gets a green colour.
I found how to add formula with conditional formatting, and everything worked fine.
However, when i did some filtering on the sheet with the payments, the conditional formatting wasn't up to date anymore, because they weren't referring to the cells that I pointed them to.
The question is: when you do conditional formatting with a formula (if cell=0,00 then this cell becomes green), is there a way that the cell linked too in your formula, will chance to whichever place it is now after filtering?
01-27-2019 05:53 PM
if you use formulas for conditional formatting rules, the devil is in the detail, especially when you use relative references without the $ sign. It's not entirely clear what you want to achieve, so please post a sample file with a (small!) data sample and explain in context.
02-04-2019 04:00 AM
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"?
02-04-2019 11:54 AM
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)
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
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
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.
02-04-2019 12:03 PM
As cosmetic comment, ISNUMBER could be missed, just
is enough for the rule formula