lock conditional formatting?

[jlam]

I am helping a client with their spreadsheet of product deadlines, etc. They have asked that, if a deadline is a week away or less, that the entire row be highlighted in a certain color. I have used conditional formatting to do this:

=IF(AND(NOT(ISBLANK($O181))), $O181<=TODAY()+7)

this is code from row O. The code from row P, would have the same formatting, but with $P181... etc. instead of $O181. (The '$' sign insures that the entire row gets formatted.

However, if the client copies and/or cuts a date to another row, the formatting goes with it. Can I lock the formatting so only the values would be moved?
Thanks,
jlam

 

[grizle]

To do it yourself you would use Paste Special.Values... easy!

You could just ask him to do that but I don't think you can default the paste function to do that as standard. If the copy/paste requirement was regular and predictable you could create a macro to paste special and put it in a command button.

 

[jlam]

Thanks Grizle,

I don't think I want to get into a macro for it. Unfortunately, when I cut the cell, and go to Paste, the Paste Special option is greyed out.

I think I have to tell them to only use manual entery and deletion.

Cheers,
jlam

 

[jlam]

Addendum:
Actually if the user just highlights the cell, it can be copied and Paste->Special-> Values without overwriting the conditional formatting.

Unfortunately, adding conditional formatting instructions with 5 rules in 180 rows increased the size of the file a megabyte to 12 meg total.

Cheers,
Jeff