Protect locked cells (only) from formatting?



Excel 2007

When you point to Home tab > Cells > Format > Protect Sheet, you get
all sorts of choices. But I'd like to be able to protect locked cells
from formatting but allow unlocked cells to be formatted. That
doesn't seem to be possible.

In other words, do anything you want to the content or formatting of
unlocked cells, do nothing to locked cells.




Gord Dibben

By default, all the cells on a sheet are "locked" when sheet is protected.

First you select all cells and "unlock" them.

Now select those you want "locked".

Apply your rules to those cells.

Protect the sheet.

Gord Dibben MS Excel MVP


This isn't what I meant.

(Also, how do you apply the protection rules (in Home tab > Cells >
Format > Protect Sheet) to cells? Once you protect the sheet, the
sheet is protected, with locked and unlocked cells behaving
differently depending on the specific checkboxes you selected in the
Protect dialog.)

I would like, when the sheet is protected, for users to be unable to
change the text and the formatting of locked cells, and to be able to
change the text and formatting or unlocked cells. The Protect dialog
does not appear to give this option.

Gord Dibben

I think you missed a point or two about locking and protecting.

I assume you do not know how to select cells and format them as locked or

Start here............................

Select all cells(CTRL + a) on the sheet

On Ribbon go to Home>Format>Format Cells>Protection.

Uncheck the "locked" option and OK your way out.

Now select only those cells you wish to be locked.

Format them to "locked"

Now change to Review Tab on Ribbon go to "Protect sheet"

You will see the list of what users are allowed to do to the locked cells.

Choose some or none then OK

Your sheet is now protected and only unlocked cells can be formatted or edited.




Gord Dibben

I missed a point or two in my reply to your reply<g>

After you hit Review>Protect Sheet, uncheck "select locked cells".

Checkmark "Format Cells" and any others you want and OK

Now you can format any cell that you can select(which would not be locked cells)


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question