Preserve conditional formatting on cut-paste

Question

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?

Answer 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.

Answer 2

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.

Answer 3

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.

Answer 4

Locking cells is not a solution, tested and does not work!

If you Paste using "Paste Special - no formatting", this will not break already formatted cells, and you don't have to worry about your user having to turn on Macros in order to fix the formatting. Easy fix/workaround for already deployed solutions!

Answer 5

This is very inelegant, but also very quick, and seems to work perfectly as far as I can tell...

Copy your conditionally-formatted cells, paste them into a Word document, copy the cells from the Word document, paste them into the target Excel sheet.

Answer 6

I had the same problem and found a little workaround - maybe usable for you:

Instead of copy & paste: mark the last row/column (or range) and use Fill Down/Fill Right action (Shortcut: strg+< / strg+>). Then the formatting is also transferred.

This can also be done with macro, i.e. for a row range:

Range("D2:D15").Select
Selection.FillDown

Instead of cut & paste: select the row or column and MOVE it by hover the cursor to the edge of the selection (the cross with arrows cursor appears) and drag & drop the row/column incl. pressing SHIFT. Then it is not a classic cut & paste, but moving. The condition formatting should be kept.

Answer 7

I found a new way to do this!

You lock the cells that are conditionally formatted. When you cut and paste, the conditional formatting stays the same!

Answer 8

I think it's a matter of how you search for the answer. This worked for me perfectly: https://www.extendoffice.com/documents/excel/3686-excel-remove-conditional-formatting-but-keep-format.html#a1