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:

Rule:

=MOD(ROW(),2)=0

Applies to:

=$1:$1048576

Action:

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
    '
        Cells.Select
        Cells.FormatConditions.Delete
    
        Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
            "=MOD(ROW(),2)=0"
        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        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