Automate Input Cell Colour

Use a CELL function option

It is best practice in Excel to have a consistent colour for input cells so that users know where they can and need to make changes. You can automate this process by using a Conditional Format.

I typically use a light-yellow background for input cells, sometimes I use the default orange background found in the Styles section on the Home ribbon – see image below.

Instead of applying a format to an input cell you could apply a conditional format that identifies unlocked cells and applies a format to only unlocked cells.


In Excel if you use sheet protection you need to unlock cells that can change – input cells – and then apply sheet protection. All cells are locked as default. Unlocking is not intuitive because it is done via a cell format. In the Format Cells dialog in the Protection tab – see below.


There is a function that identifies locked/unlocked cells. The CELL function has a protect option that returns 1 for a locked cell and 0 for an unlocked cell – see image below.

You can use the CELL function in a conditional format formula to change the cell format is unlocked.

Assuming the range you select include cell A1 the formula to use is


See the New Formatting Rule dialog below using the light-yellow fill format.

Changing a protection format will update the cell fill colour.

Check out this previous blog post for a quick way to unlock or lock a cell.

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.