Pivot Table - Conditional Format rows Depending on Latest Date

  • Hi All,


    In my pivot Table, I have dates in Col A.


    Is it possible to highlight the entire row corresponding to the latest date?


    I tried $A14 = MAX(DATE(2014,8,1),DATE(2014,9,1),DATE(2014,10,1),DATE(2014,11,1),DATE(2014,12,1),DATE(2015,1,1),DATE(2015,2,1),DATE(2015,3,1)).


    But not working.


    Thanks.

  • Re: Pivot Table - Conditional Format rows Depending on Latest Date


    You were on the right track with your CF formula :)


    CF range =$4:$15


    CF formula =$A4=MAX($A$4:$A$15)

  • Re: Pivot Table - Conditional Format rows Depending on Latest Date


    Thanks holycow!


    Its working...but when I expand a pivot field and collapse again the row highlight goes away...:(

  • Re: Pivot Table - Conditional Format rows Depending on Latest Date


    Who knew that expanding/collapsing would cause grief :confused:


    After much googling I found a solution by Luke M on another site where he suggested a macro to fix problem.


    Please see attached workbook with this macro in Sheet Module.


  • Re: Pivot Table - Conditional Format rows Depending on Latest Date


    Ok so the dynamic named range was my noob way of doing it but I have now found the proper way in a link by Jon Peltier.


    Change macro to



    Then delete the dynamic named range out of name manager.

  • Re: Pivot Table - Conditional Format rows Depending on Latest Date


    This helps immensely!


    Thanks a ton :) !!! One more new trick learnt courtesy ozgrid.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!