Conditional Formatting Not Reliably Working

David creates and distributes several large reports every week, averaging 100 rows and 66 columns. He uses Conditional Formatting to highlight information in about a dozen columns. Every week he must check all the cells to which Conditional Formatting is applied because some of the cells don't format when the data require they should. This doesn't affect all the cells, and it's not the same cells every week. Hence, David must check all of them. This is very time consuming, of course, so he wonders what he must do to make sure that Conditional Formatting behaves as it should.

There is a very good chance that Conditional Formatting is behaving as it should. I have never had any instances of Conditional Formatting rules changing on their own, and any fragmentation of the rules that I've experienced have been attributable to me (or someone else) copying and pasting information that affected those rules.

Let me provide an example: Let's say that I have a Conditional Formatting rule applied to column M. If it applies to the entire column, then deleting or inserting rows won't affect it. What would affect it, however, is if I copy a value from column C and paste that value into column M. The default method of pasting information is to paste both the value and any formatting applied to the source cell. This overwrites any formatting in the target cell, including any Conditional Formatting.

After the pasting, my Conditional Formatting rule no longer applies to all of column M. I now have a rule for all cells above where I pasted and the same rule for the cells below where I pasted. I have, in other words, fragmented my Conditional Formatting in column M, and that may present a problem like what David is experiencing.

There is no way to "lock" Conditional Formatting so that it cannot be overwritten as just described. Users can remember to only paste values (sans formatting), which would solve the issue, but there is no way to ensure that they do so. You can protect the worksheet, but that may affect other editing operations that you need to allow within it.

The best solution I've found is to create a macro that will reapply all the Conditional Formatting you need within the worksheet. In David's case, he has Conditional Formatting applied in about a dozen columns. A macro could be written to (1) remove all Conditional Formatting rules from the worksheet and (2) define and apply the proper Conditional Formatting rules to those columns. Here's a simple example to illustrate how this could work:

Sub SetRules()
    Dim rMyRange As Range

    ' Delete all Conditional Formatting in worksheet
    Cells.FormatConditions.Delete

    Set rMyRange = Columns("M:M")

    ' Define the Conditional Formatting rule for the range
    rMyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
        Formula1:="=2000"
    With rMyRange.FormatConditions(1)
        .SetFirstPriority
        .Font.Color = -16383844
        .Font.TintAndShade = 0
        .Interior.PatternColorIndex = xlAutomatic
        .Interior.Color = 13551615
        .Interior.TintAndShade = 0
        .StopIfTrue = False
    End With
End Sub

This example first deletes all the Conditional Formatting rules in the worksheet and then sets a single rule for column M. The easiest way to determine how to set up a Conditional Formatting rule via macro is to use the macro recorder. You can then condense and distill the lines generated by the recorder to get just the rule you desire, as was done in the above example. Running the macro is very, very fast, and it ensures that your desired Conditional Formatting rules are applied where they should be in your worksheet.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13934) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021.