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?

Possible duplicate of [Excel conditional formatting fragmentation](https://superuser.com/questions/598368/excel-conditional-formatting-fragmentation) — Fabian Schmied, Jan 03 '19 at 08:24
You can use [this solution](https://superuser.com/a/1696756/764600) to lock ranges of your conditional formattings. — Reza Nooralizadeh, Dec 31 '21 at 07:53

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 1 · Answer 2 · 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.

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

1

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.

answered Sep 24 '13 at 19:27

irockyoursocks

73
1
6

Given my macro, I think doing that is less work. But thanks for confirming there's no good way to do this! – durron597 Sep 24 '13 at 21:00
@durron597 I agree. I just wanted to share an alternative. My suggestion preserves all conditional formattings. While yours totally works for the OP, others may be unhappy that the macro deletes ALL conditional formattings, then applys only the one that is built into the macro. Just different. – irockyoursocks Sep 24 '13 at 21:10

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
1
3

1

What are the requirements for this new feature? Without any research, the only thing I know is that only specific builds of Excel would have this feature. **Can you provide more details?** – Ramhound Oct 21 '20 at 14:01
Excel for Mac, version 16.49 (21050901) has this feature. I was having the challenge of copy/paste of rows where columns (e.g., $A$2:$A:$1000) were conditionally formatted. This means copying row 10 to row 11 suddenly creates two rules (one for rows 2-10/12-1000 and a second one for just row 11.) Using Paste Special... followed by "All, merge conditional formats" fixes this issue. – Dave Shaver May 13 '21 at 10:40

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,091
10
23
26

answered Dec 11 '20 at 00:00

Toby

1

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

0

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.

answered Sep 14 '21 at 14:07

Alexander Chesher

1

1

This is hilarious, but not actually easier (for me) than a version with a macro where I just press ctrl+e after pasting. Still, welcome to Super User! This is probably helpful for people not doing this in Windows, so they don't have access to VBA. – durron597 Sep 14 '21 at 15:18

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
1

As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community May 17 '22 at 12:24
This does not provide an answer to the question. Once you have sufficient [reputation](https://superuser.com/help/whats-reputation) you will be able to [comment on any post](https://superuser.com/help/privileges/comment); instead, [provide answers that don't require clarification from the asker](https://meta.stackexchange.com/questions/214173/why-do-i-need-50-reputation-to-comment-what-can-i-do-instead). - [From Review](/review/late-answers/1124692) – DarkDiamond May 17 '22 at 13:19

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 -1 · Answer 9 · answered Mar 02 '17 at 15:05

-1

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!

answered Mar 02 '17 at 15:05

user703269

1

How is this different than irockyoursocks' answer? – durron597 Mar 02 '17 at 20:54
It is different as I encourage the use of PASTE SPECIAL-No Formatting. which irockyoursocks doesn't mention. DETAILS! – user703269 Apr 06 '17 at 18:35

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

-1

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.

edited Dec 08 '15 at 06:46

phuclv

26,555
15
113
235

answered Nov 10 '14 at 16:16

Chris

182
1
2
11

1

FYI: Strg is Ctrl in German layout. And the shortcut for fill down/fill right is ctrl+D/ctrl+R respectively – phuclv Dec 08 '15 at 06:47
1

Fill down/right isn't a good substitute for cut+paste, and drag-to-move also moves my conditional formatting – Dan Cecile Nov 24 '16 at 17:58

score -1 · Answer 11 · 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

7

Congratulations on your discovery. Can you edit your answer to share the details of how to lock the cells that are conditionally formatted? – fixer1234 Mar 03 '15 at 20:38
1

I would be likely to upvote and even accept this answer if you edit it as described by @fixer1234 – durron597 Mar 04 '15 at 01:35

score -2 · Answer 12 · 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 13 · 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:

13 Answers13

Linked