Colour locked Cells that are protected

[George1950]

How can I colour cells in excel that are locked and the sheet is protected.

I have tried ticking the format cells box when applying the protection, but it keeps defaulting back to that box being unticked.

[#2]

@George1950 

If you want to protect the sheet,

go to - Extras --> Protection --> Protect sheet

-- if you select this, the "Protect sheet" window opens.

You can assign a password at the top and in the box below

define what users can and cannot do.

Put a checkmark in front of "Format cells".

Now you can format the cells despite password protection.

I would be happy to know if I could help.

NikolinoDE

Was the answer useful? Mark them as helpful!

This will help all forum participants.

[#3]

Hi @NikolinoDE 

Thanks for your reply.

Have done exactly what you suggested and it works, while I stay on that tab.

But when go to another tab amd come back to the tab I want to colour the cells it defaults back to there being no tick in the 'Format Cells' checkbox. The same thing happens when I close the file and reopen it again.

Best Regards,

George1950

[#4]

@George1950 

It would be an advantage if you could see the file (without sensitive data) to see where the error could be.

Knowing the Excel version and the operating system would also be also an advantage.

Here is an example file with a locked sheet where you can change the color.

Thank you for your patience and time.

NikolinoDE

I know I don't know anything (Socrates)

[#5]

@NikolinoDE 

Thanks for the example file, it does exactly what I want my file to do.

I am using Excel 365 whicch is the subscription version with Window 10 Pro operating system.

My file is complicated with 20 tabs, but I only want to colour the cells in one of the tabs.

Cant understand why the format cells check box defaults back to NO Tick in it.

Thanks for your interest 

George1950

"I cannot teach anybody anything" (Socrates)

[#6]

@George1950 

Protect a worksheet

To prevent other users from accidentally or deliberately changing, moving, or deleting data in a worksheet, you can lock the cells on your Excel worksheet and then protect the sheet with a password. Say you own the team status report worksheet, where you want team members to add data in specific cells only and not be able to modify anything else. With worksheet protection, you can make only certain parts of the sheet editable and users will not be able to modify data in any other region in the sheet.

Important: 

• Worksheet level protection is not intended as a security feature. It simply prevents users from modifying locked cells within the worksheet.

• Protecting a worksheet is not the same as protecting an Excel file or a workbook with a password. See below for more information:

• To lock your file so that other users can’t open it, see Protect an Excel file.

• To prevent users from adding, modifying, moving, copying, or hiding/unhiding sheets within a workbook, see Protect a workbook.

• To know the difference between protecting your Excel file, workbook, or a worksheet see Protection and security in Excel.

Hope I was able to help you with this info.

Thank you for your understanding and patience

NikolinoDE

Only he who knows that he is not wise is wise. (Sokrates)