Protect Cell Formatting including Conditional Formatting
I'm working with a Government client in the UK to collect and then analyse
data from 400+ respondents. For a variety of reasons (too boring to discuss)
we couldn't use the web so after much discussion we've used Excel to provide
a questionnaire and a simple collation mechanism to extract the data from
multiple responses for analysis. To avoid security complications we have
agreed NOT to use macros. The audience is NOT assumed to be experienced with
The questionnaire is pretty much finished, with lots of lookups (to
pre-complete forms where possible), lots of validation checks, very nicely
presented and with lots of hyperlink arrows to guide them through. Excellent
feedback from the initial sample. The workbook and sheets are protected
The only fly in the ointment (though minor) is that if they copy a cell and
paste it into an input cell the conditional formatting for that cell is lost.
While this isn't a serious problem I'd rather stop it if possible.
Does anyone know a way to do this ? When the sheet is protected there are a
host of things which can be turned on/off including formatting. This is all
protected, but the paste operation seems to override the protection. I could
try and explain to users that they should use Paste-Special and Values, but
it's more complicated than I wanted to make it.