+ Reply to Thread
Results 1 to 8 of 8

Conditional formatting constantly breaking when adding rows

  1. #1
    Forum Contributor
    Join Date
    05-28-2010
    Location
    Antwerp, Belgium
    MS-Off Ver
    Office 365
    Posts
    170

    Conditional formatting constantly breaking when adding rows

    I am setting a conditional formatting for a column D:D for example. (Going to call it CC)

    Then I add a row in between, lets say row 4, BOOM. The whole CC for that column breaks..
    When I open the CC I see that it split the range to C1:C3,C5:C106..

    Same problem happens with pivot tables. There even when changing from one filter to another, it will break! Yes, I know I can set columns inside the pivot table instead, but I am trying to understand why does this happen.

    I can't even imagine for whom such a stupid functionality could be useful.. If I set a CC for the WHOLE column, would I want the CC not to apply for the row just because I added it now??

    Thanks

  2. #2
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,637

    Re: Conditional formatting constantly breaking when adding rows

    conditional formats are very finicky like that
    are you using absolute references when applying conditional format?

    also pivot table conditional formats are slightly different to normal ones
    http://www.contextures.com/excelpivo...nalformat.html

    are you able to supply sample data to see exactly what is happening?
    as conditional formats are very data structure specific
    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Forum Contributor
    Join Date
    05-28-2010
    Location
    Antwerp, Belgium
    MS-Off Ver
    Office 365
    Posts
    170

    Re: Conditional formatting constantly breaking when adding rows

    Before uploading a data sample, I want to answer your question. I am putting: $B:$B in the range it applies to.

    I can't do anything else, if I put B:B it will automatically make it $B:$B anyway..

    Am I missing something?

  4. #4
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,637

    Re: Conditional formatting constantly breaking when adding rows

    sorry let me rephrase

    "refers to" should be absolute
    its if your using formula to based CF then that can be relative

    also when your adding a row
    are you adding a complete row? as in row insert or just certain amount of columns that doesnt cover the CF "refer to" range?

  5. #5
    Forum Contributor
    Join Date
    05-28-2010
    Location
    Antwerp, Belgium
    MS-Off Ver
    Office 365
    Posts
    170

    Re: Conditional formatting constantly breaking when adding rows

    Ok, I did couple of observations:

    1. This happens mainly when I am using a formula as the condition. In my case its: =MONTH($B6)<>MONTH($B7)
    2. It happens in both cases, when I add a whole row or just a row to the table. It makes two two conditions out of one.
    3. It even happens if I will add a row by doing CTRL+D (in that case excel is copying the value from the cell above and automatically extends the table and creates a new row)
    4. Doesn't happen when I fill in a new entry regularly (that means go to the last row of the table and input something in the cell below it, then excel automatically extends the table and creates a new row)

    What do you think?

  6. #6
    Forum Contributor
    Join Date
    05-28-2010
    Location
    Antwerp, Belgium
    MS-Off Ver
    Office 365
    Posts
    170

    Re: Conditional formatting constantly breaking when adding rows

    humdingaling Bump!

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,110

    Re: Conditional formatting constantly breaking when adding rows

    Not sure if this will have any affect, but you should avoid using entire columns for CF, it could start to slow your file down

    Then, using this formula...
    =MONTH($B6)<>MONTH($B7)
    If you insert a row at (above) 7, then $B6 will now be comparing against $B8, not the (new) row 7

    You were asked for a sample workbook, perhaps that will also shed some light?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  8. #8
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Conditional formatting constantly breaking when adding rows

    Using Excel 2010:

    I selected D1:D20 and went to Conditional Formatting, New Rule.., Use Formula and entered this formula and chose formatting:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    In the applies to, the range $d$1:$d$20 was automatically entered.

    Don't use full column references.

    I inserted new rows in the middle of the data and the conditional formatting maintained its definition but with an expanded range. I did this several times and the CF didn't break.

    I don't know what version of Excel that the Contextures video cited was based on, but I didn't have the problem demonstrated when testing. Maybe I was just lucky. I don't know.
    ----
    Discovered that I was using the method described when Pivot Table not created by data in a table....duh!!
    Last edited by newdoverman; 06-20-2015 at 06:59 PM.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

+ 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. Replies: 0
    Last Post: 11-21-2014, 09:25 AM
  2. Constantly adding items to a working macro
    By lukeduggan in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-22-2014, 07:34 AM
  3. Web Data Links Constantly Breaking
    By QPRMinty in forum Excel General
    Replies: 2
    Last Post: 10-16-2014, 02:04 AM
  4. Conditional Formatting when adding rows
    By airfrc1 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-29-2014, 08:42 AM
  5. lose range conditional formatting adding/deleting rows
    By mrodenkirch in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-29-2013, 12:46 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