+ Reply to Thread
Results 1 to 5 of 5

Permanent Conditional Formatting for Table

  1. #1
    Registered User
    Join Date
    05-31-2013
    Location
    USofA
    MS-Off Ver
    Excel 2010
    Posts
    6

    Permanent Conditional Formatting for Table

    Good day,

    I have a table that contains +/- 12,000 (A6:R12266) rows and I use several conditional formatting rules to highlight rows where the data changes from the row above in a sorted column for the visual easy of users. The conditional formatting works great however the data is the spreadsheet is constantly being modified and that is where I run into the problem. Whenever a row is inserted or deleted my rule is divided into multiple rules which is tedius to manage and also causes the formatting to appear incorrectly in some cases. I have the table defined as a named table in the name manager and I use that name for the range in the "Applies to" in conditional formatting, but as soon as I choose apply the name is converted to the current range which becomes several seprate rules and ranges as soon as the table is modified.

    I dont know if the problem is in my formula or elsewhere but any help would be greatly appreaciated.

    Formula to define which rows are highlighted
    =$F5<>$F6

    Range data applis to after chosing the tables name and clicking apply
    =$A$6:$R$12266

    Thanks
    Jamie

  2. #2
    Forum Expert
    Join Date
    06-26-2010
    Location
    Austin, TX
    MS-Off Ver
    Excel 2010
    Posts
    1,673

    Re: Permanent Conditional Formatting for Table

    I have the table defined as a named table in the name manager
    Is this a table that you created with Ctrl-T? In other words, when you select a cell in the table, does Excel show a new 'Table Tools -> Design' ribbon? If not, then try creating an Excel table (using Ctrl-T); otherwise, I am unsure as using a table usually fixes this type of 'split' conditional formats. Also, be sure to use the table's name and column names in the conditional formatting formula.
    Pauley
    --------
    If I helped with your issue, I'd appreciate a rep bump (hit the '*' icon to the bottom left of this post).

  3. #3
    Registered User
    Join Date
    05-31-2013
    Location
    USofA
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Permanent Conditional Formatting for Table

    Thanks for the response Pauly. Yes the table was created with Ctrl+T. I creates an example if you will see the attached file. First look at the formatting, you will see it correctly highlights row when value in first column changes from the row above. Now try adding a few rows and deleting a few rows. Note how the formatting stops working correctly and open the conditional formatting to see how it divides the rule into multiple rules which arent functioning. Any ideas how to resolve or avoid this beside going in and fixing formating whenever table is modified?Table Formatting Issue.xlsx

  4. #4
    Forum Expert
    Join Date
    06-26-2010
    Location
    Austin, TX
    MS-Off Ver
    Excel 2010
    Posts
    1,673

    Re: Permanent Conditional Formatting for Table

    OK, I see. Try changing the rule to:
    =$B3<>OFFSET($B3,-1,0)

  5. #5
    Registered User
    Join Date
    05-31-2013
    Location
    USofA
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Permanent Conditional Formatting for Table

    PaulyB that seemed to do the trick. Formula still achieves desired formating and this formula is not affected by inserting or deleting rows. Thank you for your help!
    Jamie

+ 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. Emulating conditional formatting in VBA, but making it permanent.
    By lankyfish in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-19-2013, 12:36 PM
  2. Pivot Table Permanent Formats
    By BoothHopkins in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 09-09-2013, 05:03 PM
  3. Permanent Conditional Formatting Setting
    By gaspower in forum Excel General
    Replies: 2
    Last Post: 11-14-2012, 01:18 AM
  4. Make conditional formatting permanent
    By nsv in forum Excel General
    Replies: 0
    Last Post: 10-07-2008, 11:25 AM
  5. [SOLVED] make custom formatting permanent
    By simha in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-15-2005, 07:35 PM

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