Forum Discussion

Beaverbear's avatar
Beaverbear
Copper Contributor
Jan 27, 2019

Conditonal formatting - filter issue

Hi,

 

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?

 

Thanks! 

  • Beaverbear's avatar
    Beaverbear
    Copper Contributor

    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.

       

       

       

       

       

      • SergeiBaklan's avatar
        SergeiBaklan
        MVP

        Hi Ingeborg,

         

        As cosmetic comment, ISNUMBER could be missed, just

        =MATCH($A2,Names!$A:$A,0)

        is enough for the rule formula

  • Hello,

     

    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.

     

Resources