How do you Preserve conditional formatting when users cut and paste WITHOUT having to lock the sheet?

[willow1985]

I have multiple conditional formats (9 of them) that are pretty basic

Formula: =$B3="FIRM" [format text red], Applies to: =$A$3:$Z$1662

However users copy and past data into the table (named MF) of the spreadsheet frequently and when this happens suddenly the ranges and formulas get all messed up or it creates multiple conditional format rules.
I do not wish to lock the sheet as that will cause other complications I am trying to avoid.
Is there any way to preserve these conditional formats that the rules do not change when pasting row data in the table?

Any help would be appreciated. I have googled this topic previously but have not found a solution to date.

Thank you

Carla

 

[Jeffrey Mahoney]

No good solutions. I've used macros to heal the Conditional Formatting (CF), by raw code and by having a hidden row that gets copied down. I have blocked the ability for the user to paste anything except by using a paste button that only pastes values.

Macros to heal the CF don't allow for new CF ranges unless you add them to the macro. If you change your CF rules you have to make sure to copy those new rules to the hidden row. Blocking the ability to paste is not easy nor foolproof.

You could create a macro that pastes all cell formatting except for CF and encourage your users to use that instead of normal paste. Good luck!

I wish MS would allow us to select whether a CF can propagate to other cells. Or have the ability to turn off CF when pasting.