Conditional formatting disappears when cell contents deleted
 

I'm having a problem with a conditional format formula disappearing from a cell when I paste another cell's contents into it and then delete the content.

I always thought that conditional format formulas were separate from regular cell contents (like numbers, text, formulas). So if I have a conditional format in a cell, then put some content in it, then delete the content, the conditional format should still apply to that cell.

I have a red border conditional format in a cell, that says if the cell 0 (i.e., if there's content in it) the border should not appear. The CF rule works fine, and when I paste the content of another cell into the CF cell the border disappears as it should. But then, if I delete the content, the CF format disappears from the cell: The Conditional Formatting Rules dialog box shows no formula is in that cell.

The cell whose contents I copied had no conditional format.

In case it's useful, here's the CF formula:

=IF(AND($K$1920,$L$1920,$M$192=0),TRUE,FALSE)

All cells involved have a standard format as well; they're all date formats. I'm using Excel 2007.

Use "paste special" and either "values" or "formulas" as appropriate.

That way the conditional formatting will remain.

Conditional formatting disappears when cell contents deleted
 

Hi,

Am Sat, 21 Jul 2012 17:04:30 +0000 schrieb wardw:

I'm having a problem with a conditional format formula disappearing from
a cell when I paste another cell's contents into it and then delete the
content.

if you paste another cells contents you paste all, also the cells
format. Therefore the CF disappears. Copy the cell and then Paste
Special = Paste values.
And the formula for CF you can shorten:
=AND(K192*L1920,M192=0)

Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Thank you, Spencer101 and Claus Busch; I'm now using Paste Special = Paste Values and the CF remains in the cell. And thanks for your shorter formula, Claus; it works fine.