Thread: Conditional formatting: Copying and pasting a whole row chops up the defined range!

Hi!

I have applied conditional formatting to column A. The range defined in the "Manage rules" section of "Conditional formatting" is Sheet1!$A:$A.

I can insert a row in the workbook and the range defined will still be Sheet1!$A:$A. But if I copy more than one cell of a row and paste the copied cells above or below, the defined range gets chopped up. Example: If I copy cells A11:C11 and paste them onto A10:C10, I get two rules instead of one in the "Manage rules" section; one for the range Sheet1!$A10 and one for the rest of column A: Sheet1!$A$1:$A$9;Sheet1!$A$11:$A$1048576.

Is there a way to keep the rules and ranges as they were first defined even if I copy and paste cells onto each other?

Copying and pasting will not screw up that formatting. Cutting and Pasting will. The safest bet is to copy and Paste Special Values which leaves formatting of new cell the same as it was initially.
When you cut and paste, you cut the format from the original cell also so that cell no longer has your conditional formatting.