28

Often, I create a sheet with conditional formatting, and set the cell ranges so that the conditional formatting rules are only applied once to a range of cells, e.g.

Make $A$1:$A$30 red and
Make $B$1:$B$30 blue.

After inserting/deleting a number of rows and/or columns the conditional formatting rule set becomes very fragmented, with the same rules repeated for a different ranges. e.g.

Make $A$1:$A$2 red 
Make $A$3:$A$4 red 
Make $A$5:$A$9 red
Make $A$10:$A$20 red 
Make $A$21:$A$30 red
...
Make $B$1:$B$2 blue 
Make $B$3:$B$4 blue
Make $B$5:$B$9 blue
Make $B$10:$B$20 blue 
Make $B$21:$B$30 blue
....

Is there a good practice way of preventing this, or am I doomed to cleaning up the rule sets manually when they get too messy?

1

4 Answers 4

16

Inserting and deleting rows does not cause conditional formatting to get fragmented.

The cause is copy/pasting between cells or rows using the standard copy/paste. The fix is to always use paste-value or paste-formula. On the destination right click and the Paste Options section will offer 123 (values) and f (formulas). Don't copy/paste formatting as that causes the conditions to get copy/pasted and sometimes they will be fragmented.

When you do a standard copy/paste it also copies the cell's conditional formulas. Let's say you have two rules:
1) Make $A$1:$A$30 red
2) Make $B$1:$B$30 blue
Now select A10:B10 and copy/paste that to A20:B20. What Excel will do is to delete the conditional formatting for A20:B20 from the rules that applied to those cells and add new rules that have the formatting for A20:B20. You end up with four rules.
1) Make =$A$20 red
2) Make =$B$20 blue
3) Make =$A$1:$A$19,$A$21:$A$30 red
4) Make =$B$1:$B$19,$B$21:$B$30 blue
Had you copy/pasted just A10 to A20 Excel would have noticed the same rule applied to both the source and destination and does not fragment the rules. Excel is not smart enough to figure out how to avoid fragmentation when your copy/paste impacts two or more conditional formats.

Inserting and deleting rows does not cause fragmentation as Excel simply expands or shrinks the condition rule(s) that cover the area where you did the row insert or delete.

Someone suggested using $Q:$Q rather than $Q$1:$Q$30. That does not help and you will still get fragmentation when you copy/paste cell formatting as noted above.

4
  • Also note that Cut <kbd>ctrl-x<kbd> will delete the conditional formatting from the cell cut(now the cell has no formatting), and paste the formatting into the new cell. I'll bet someone out there knows how to prevent this? Apr 30, 2017 at 18:05
  • @user135711 It depends on what you are trying to do. By default, copy and cut both pick up the formulas, formatting, and links of the source cell with cut also removing the same from the source cell. If the intent is to remove the formula/value from the source but to keep the formatting I'll copy/paste the stuff to the destination and then delete the source. Delete (the Del key) only clears the formula or value. It leaves the formatting and links in place. May 1, 2017 at 20:13
  • 1
    The INDIRECT() function upvoted as an answer in another version of this question doesn't work because it's evaluated to the cells when used in the applies to field just as if you entered cells manually. May 2, 2017 at 14:51
  • superuser.com/a/1113566/79488 has a good workaround for cleaning up the fragmented rules if you used copy/pase Jan 3, 2019 at 8:25
5

Had the same problem when applying conditional format to a column of table. When adding rows, I found it works best to apply the rule to the entire column using $A:$A, or whichever column.

enter image description here

1
  • 1
    In my experience, when the worksheet gets a bit bigger, doing this will cause the conditional formats to become bloated and calculations can be reduced to a crawl.
    – rohrl77
    Dec 16, 2019 at 15:44
5

(This is a workaround, so I was going to put it as a comment, but I don't have enough reputation.)

Unfortunately it seems that you are doomed to clearing up rule sets when they get messy.

An easy way to do this is to create a worksheet containing the formatting you require, but no data. This can be in the same workbook as your original worksheet, or in another workbook you keep as a template.

When you need to clean up, go to this worksheet, right-click the Select All button, choose the Format Painter, then click the Select All button on your original worksheet. The formats are overwritten with the untainted version.

2

Copying/pasting/cutting/inserting cells manually causes the problem and it is hard to avoid it.

Problem solved through VBA macro.

Instead of copying/pasting/cutting/inserting cells manually, I do it through an Excel macro, which preserves the cell ranges (activated via a button).

Sub addAndBtnClick()
    Set Button = ActiveSheet.Buttons(Application.Caller)
    With Button.TopLeftCell
        ColumnIndex = .Column
        RowIndex = Button.TopLeftCell.Row
    End With
    currentRowIndex = RowIndex
    Set Table = ActiveSheet.ListObjects("Table name")
    Table.ListRows.Add (currentRowIndex)
    Set currentCell = Table.DataBodyRange.Cells(currentRowIndex, Table.ListColumns("Column name").Index)
    currentCell.Value = "Cell value"
    Call setCreateButtons
End Sub

Sub removeAndBtnClick()
    Set Button = ActiveSheet.Buttons(Application.Caller)
    With Button.TopLeftCell
        ColumnIndex = .Column
        RowIndex = Button.TopLeftCell.Row
    End With
    currentRowIndex = RowIndex
    Set Table = ActiveSheet.ListObjects("Table name")
    Table.ListRows(currentRowIndex - 1).Delete
End Sub

Sub setCreateButtons()
    Set Table = ActiveSheet.ListObjects("Table name")
    ActiveSheet.Buttons.Delete
    For x = 1 To Table.Range.Rows.Count
        For y = 1 To Table.Range.Columns.Count

            If y = Table.ListColumns("Column name").Index Then
                Set cell = Table.Range.Cells(x, y)
                If cell.Text = "Some condition" Then
                    Set btn = ActiveSheet.Buttons.Add(cell.Left + cell.Width - 2 * cell.Height, cell.Top, cell.Height, cell.Height)
                    btn.Text = "-"
                    btn.OnAction = "removeAndBtnClick"
                    Set btn = ActiveSheet.Buttons.Add(cell.Left + cell.Width - cell.Height, cell.Top, cell.Height, cell.Height)
                    btn.Text = "+"
                    btn.OnAction = "addAndBtnClick"
                End If
            End If
        Next
    Next
End Sub

To reset formatting (not really needed):

Sub setCondFormat()
    Set Table = ActiveSheet.ListObjects("Table name")
    Table.Range.FormatConditions.Delete
    With Table.ListColumns("Column name").DataBodyRange.FormatConditions _
        .Add(xlExpression, xlEqual, "=ISTLEER(A2)") 'Rule goes here
        With .Interior
            .ColorIndex = 3 'Formatting goes here
        End With
    End With
    ...
End Sub
1
  • 3
    Please do not post the same answer to multiple questions. If the same information really answers both questions, then one question (usually the newer one) should be closed as a duplicate of the other. You can indicate this by voting to close it as a duplicate or, if you don't have enough reputation for that, raise a flag to indicate that it's a duplicate. Otherwise tailor your answer to this question and don't just paste the same answer in multiple places.
    – DavidPostill
    Jul 26, 2017 at 11:09

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .