Protect conditional format but unprotect cell content

I would like to create a sheet which sets the cell color to red if the value does not follow certain criteria. I do this successfully by conditional formatting.
Now my main problem: people will copy paste content into the cells and overwrite the formatting. Is there a way to protect the format of all cells but allow the user to change the content?

I can not use makros for data validation.

Thank you very much for you help!

1 Like

I posted this on bugzilla a couple of months ago.

Regrettably, there seems to be no generally-acceptable solution. Furthermore, according to bug 88108 which I cited, the difficulty is related to the odf file structure; I ask myself why that fault was allowed to become part of a specification that seems to be difficult to modify.

This may appear to be a relatively trivial issue, but it is one that renders LO totally unusable for many educational and business purposes. I’m seriously worried that the FOSS movement (including recent developments in Munich) will be compromised by such failings.

I understand from your final comment that, in your situation, writing macro software is not an allowable workaround. This is usual in many regulated and other working environments. However, there are many non-critical cases where Microsoft’s VBA macro development environment can be used, for example in education (making flashcards…). VBA has a huge ecosystem on the web where you can usually find suitable macros and code snippets (there are plenty of flashcard macros to choose from). The LO macro environment has a learning curve that makes it unacceptable for amateur and occasional users, thus reinforcing the dominance of Excel.

the difficulty is related to the odf file structure

It’s not a “difficulty”. It’s the same as with countless other changes we do. We change file structure as an LO’s extension, then suggest it as a sample implementation of a proposal to the ODF committee (a required prerequisite).

I ask myself why that fault was allowed to become part of a specification that seems to be difficult to modify

Even if that was so, the question itself is like “why it was allowed that something imperfect happened on this Earth?”. The question doesn’t make sense, just as with following musing on FLOSS destiny. People write specs, and may make mistakes. Your expertise is always welcome in the review and proposals, the process is open.

The problem needs its developer - and for that to happen, the problem must either bother the developer themselves, or some paying customer. And any contribution is welcome!