Excel – Preserve conditional formatting on cut-paste

conditional formattingcut-and-pastemicrosoft excelmicrosoft-excel-2010

I have some data from different sources that I'm trying to line up to get a good handle on which fields they have in common and which they don't. To make it easier to see rows, I set up a conditional formatting rule like this:



Applies to:



Turn Green

This works great… until I cut and paste a block of cells in one column or another. Excel's "intelligent cut-and-paste" breaks everything, by either duplicating rules, or removing sections from the region, and I have to go fix the conditional formatting again. How can I move the data around without changing the coniditional formatting rules?

Best Answer

  • I managed to find one solution, I recorded this macro:

    Sub FomattingRules()
    ' FomattingRules Macro
    ' Keyboard Shortcut: Ctrl+e
        Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        With Selection.FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorAccent3
            .TintAndShade = 0.599963377788629
        End With
        Selection.FormatConditions(1).StopIfTrue = False
    End Sub

    Technically this works but I'd rather find a solution that doesn't require me to press ctrl+e every so often. But it's better than resetting the rules by hand, I guess.

  • Related Question