+ Reply to Thread
Results 1 to 4 of 4

Conditional Formatting of alternating rows that adjusts after hiding rows.

  1. #1
    Registered User
    Join Date
    06-18-2020
    Location
    Washington DC
    MS-Off Ver
    Office 365
    Posts
    6

    Conditional Formatting of alternating rows that adjusts after hiding rows.

    I have read many posts in many places with various Formulas for FILTERED row formatting. This is not filtered. There are many places on a report that we may hide rows if that data is irrelevant for this report/presentation kit. I read that it is potentially possible in VBA but it sounds like heavy code if it needs to be done across multiple sheets in select areas that may be dynamic (someone may add a row messing up a static code). I read it may not be possible but I've read that it also may be possible with a helper Column. Has anyone had any luck with having grey/no fill alternating rows in a specific range that updates fill as rows are hidden?

  2. #2
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Conditional Formatting of alternating rows that adjusts after hiding rows.

    Assuming col A will always have a value and you have a header in row 1 with data starting in row2. You could put
    =SUBTOTAL(103,A$2:A2)
    into an empty column & fill down.
    Then use
    =MOD($X2,2)=0
    in CF, changing X to the column with the subtotal.

  3. #3
    Registered User
    Join Date
    06-18-2020
    Location
    Washington DC
    MS-Off Ver
    Office 365
    Posts
    6

    Re: Conditional Formatting of alternating rows that adjusts after hiding rows.

    Wow, that works quite well. Thanks! Is there an easy way to have it start with the formatted color?

  4. #4
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Conditional Formatting of alternating rows that adjusts after hiding rows.

    Yup, just use
    =MOD($X2,2)=1

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Formatting alternating blocks of rows
    By ngbc in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-12-2018, 08:26 AM
  2. formatting rows with alternating colors
    By seemore in forum Excel General
    Replies: 2
    Last Post: 10-17-2014, 03:20 PM
  3. [SOLVED] Colour conditional formatting alternating blocks of rows
    By Deanomcbeano in forum Excel General
    Replies: 7
    Last Post: 10-03-2012, 01:48 PM
  4. Excel 2007 : Hiding rows through conditional formatting
    By TheCoatHanger in forum Excel General
    Replies: 1
    Last Post: 01-12-2010, 07:59 PM
  5. Conditional Formatting - Hiding Rows
    By buddhajb in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-30-2008, 07:17 PM
  6. Conditional Formatting - Hiding Rows
    By buddhajb in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-30-2008, 03:56 PM
  7. Conditional formatting similar to shading alternating rows
    By Conan Kelly in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 08-22-2006, 06:20 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1