Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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:



Applies to:



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?

share|improve this question

I managed to find one solution, I recorded this macro:

Sub FomattingRules()
' FomattingRules Macro
' Keyboard Shortcut: Ctrl+e

    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
    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.

share|improve this answer

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.

share|improve this answer
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

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!

share|improve this answer
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 3 '15 at 20:38
I would be likely to upvote and even accept this answer if you edit it as described by @fixer1234 – durron597 Mar 4 '15 at 1:35

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:


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.

share|improve this answer
FYI: Strg is Ctrl in German layout. And the shortcut for fill down/fill right is ctrl+D/ctrl+R respectively – Lưu Vĩnh Phúc Dec 8 '15 at 6:47

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.