Preserve conditional formatting on cut-paste

Solution 1:

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.

Solution 2:

There's no great way to get around this. I recommend doing Copy+Paste Values, then deleting the contents of the original cells. This will leave all formatting unchanged, but move cell contents successfully. It's a little cumbersome, but your other option is to redo the Conditional Formatting every time you cut and paste. You decide which is less work.

Solution 3:

Not being as talented of a programmer as some of you are, what I did to solve the problem is a short two step process. first, I created one additional line of data separate from the data field I am working on for my project. I made sure that this data line has the correct conditional formatting I want to use across the entire data field. For convenience, I titled this line "Standard".

Next, I simply select the "Standard" line by highlighting all of the cells which have the formula of which I wish to copy. Third, I use the "format painter" function. I click "format painter" (upper left corner of the screen below cut and copy), and then highlight the cells that I want the formula to match the formula preserved in the "Standard" line.

It isn't automatic, but once you create the "Standard" line preserving the conditional formatting you want to use, it is very easy to apply to even a very large data field in just two steps.