+ Reply to Thread
Results 1 to 4 of 4

Keeping cells format after filtering

  1. #1
    Forum Contributor
    Join Date
    11-28-2008
    Location
    Reykjavík, Iceland
    MS-Off Ver
    Any of: 2003 & 2007
    Posts
    412

    Keeping cells format after filtering

    Hi,

    Sheet1, Col. A has "Auto-Filter".

    The cells were C.F., by 2 different Interior colors, as per Even/Odd rows.

    So far so good.

    The problem starts when the list is filtered (loosing the requested format).

    I'm looking for a solution that will keep the same format when the range is filtered/not filtered.

    Cell E1 was added in order to force recalculation because I didn't find a filtering event.

    I hope I made myself clear.

    Thanks, Elm
    Attached Files Attached Files
    Last edited by ElmerS; 04-22-2010 at 12:17 PM.

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Keeping cells format after filtering

    Use SUBTOTAL

    With A2:A20 highlighted the rules would be:

    =MOD(SUBTOTAL(3;A$1:A2);2)=0

    and

    =MOD(SUBTOTAL(3;A$1:A2);2)

  3. #3
    Forum Contributor
    Join Date
    11-28-2008
    Location
    Reykjavík, Iceland
    MS-Off Ver
    Any of: 2003 & 2007
    Posts
    412

    Re: Keeping cells format after filtering

    Excellent !

    Thanks.

    btw: Is there any other way to fire a filter event without the use of an helper formula ?

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Keeping cells format after filtering

    No, I don't believe so - you need the formula to make use of the fact that altering row visibility is in itself a Volatile action.
    Last edited by DonkeyOte; 04-22-2010 at 12:27 PM. Reason: removed link to article - content superfluous

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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