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?

score 3 · Accepted Answer · answered Sep 24 '13 at 14:09

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.

score 2 · Answer 2 · answered Sep 24 '13 at 19:27

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.

score 1 · Answer 3 · answered Dec 23 '16 at 15:27

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.

Sergey Kraev · Answer 4 · 2020-10-23T14:30:47.653

0

In Excel 365 there is a new paste mode: "Paste Special - Merge Conditional Formatting (G)".

edit 1: I'm not sure what the requirements are for this new feature. I'm using default Office installation. In fact, it is also present in Excel 2016. The build numbers are:

Excel 2016 (16.0.5071.1000) Excel 365 (16.0.13127.20266)

edited Oct 23 '20 at 14:30

answered Oct 21 '20 at 13:34

Sergey Kraev

11

score 0 · Answer 5 · edited Dec 11 '20 at 00:19

0

I think if you do Conditional Formatting by Column (i.e. $G:$G) instead of mentioning rows, when you cut and paste rows it should preserve conditional formatting. This is provided that you don't cut and paste columns (i.e. if you use $A:$D).

edited Dec 11 '20 at 00:19

Stephen Rauch

3,296

answered Dec 11 '20 at 00:00

Toby

1

score 0 · Answer 6 · answered Sep 14 '21 at 14:07

If I copy the selected cells with conditional formatting and paste them into a 'Google Sheet,' the conditional formatting stays with the cells. I can then copy and paste them back into an excel sheet where the conditional formatting still stays.

It's the least cumbersome and fastest way i've found.

Sergey · Answer 7 · 2022-05-17T14:17:16.303

0

The easiest way is - to paste directly into the Formula Bar, in case if you input cell-by-cell data.

edited May 17 '22 at 14:17

answered May 17 '22 at 12:04

Sergey

1

score 0 · Answer 8 · answered Jul 17 '22 at 23:58

0

Copy selected cells , select paste special and then select "all using source scheme". It works perfectly.

answered Jul 17 '22 at 23:58

user1711242

1

score 0 · Answer 9 · answered Nov 15 '24 at 07:58

The original problem, i.e. marking odd and even rows with different colours can be solved without conditional formatting and hence without the resulting issues.

Convert the relevant range to a "table": Select the desired range, then press ctrl + t (or you can press the format as table button in the home tab or press the table button in the insert tab). Then choose a table style with alternating colours for the rows.

score -1 · Answer 10 · answered Mar 02 '17 at 15:05

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!

score -1 · Answer 11 · edited Dec 08 '15 at 06:46

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.

score -1 · Answer 12 · answered Mar 03 '15 at 20:08

-1

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!

answered Mar 03 '15 at 20:08

Frankie P

1

score -2 · Answer 13 · answered Mar 13 '18 at 15:14

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.

score -3 · Answer 14 · answered Aug 02 '18 at 18:23

-3

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

answered Aug 02 '18 at 18:23

Josh

1

Preserve conditional formatting on cut-paste

Rule:

Applies to:

Action:

14 Answers14

Linked