Lock cell formatting but allow value input

[geekgirlau]

Situation:

• I have a report used for tracking work, with milestones (dates) for various stages.
• A "traffic light" colour is applied (via VBA) when the user changes the status of an item (green = on track, amber = slipping, red = will miss delivery).
• While MS Project would be a much better option to handle this, I am restricted to using Excel (for a variety of reasons).

What I'd like to achieve:

• Users may change the planned milestone dates during the course of the work.
• I do NOT want users to be able to change the traffic light colour (which I can foresee is going to occur because they want the progress to look better than it actually is!).

Is it possible to allow users to enter a value into a cell, but NOT allow them to change the cell format (i.e. lock the format only, not the content)? Alternatively, to detect that they have attempted this and reverse it? There's no sheet-level event that would handle this that I'm aware of.

[Bob Phillips]

Why don't you trick them?

Have the input cell referenced somewhere else off-stage, and use the rules to colour this off-stage cell. Then use the camera tool to copy the view of that cell onto the traffic-light-cell.