Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question

4 Answers 4

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.

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 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 FillDown/FillRight 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.

share|improve this answer

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
2  
Congratulations on your discovery. Can you edit your answer to share the details of how to lock the cells that are conditionally formatted? –  fixer1234 2 days ago
    
I would be likely to upvote and even accept this answer if you edit it as described by @fixer1234 –  durron597 2 days ago

Your Answer

 
discard

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.