Locking cells except for one colour

[MattKoleczko]

I've found various threads on this, but not one that seems to work properly.

I have a workbook with multiple sheets that I need to lock the contents of except for the cells that are light blue. Colour code is 20, I believe.

Not sure if it makes a difference, but the cells either require data to be entered by the user or to choose an option from a dropdown box.

Additionally Id like to hide all formulas so they're not visible to the end user.

Much appreciate your help.


Cheers,
Matt

[kev_]

Try this (untested - on my iPad!)
Remember to lock all the worksheets first.

Please Login or Register  to view this content.

To find the current color index of a cell, select the cell and run this macro

Please Login or Register  to view this content.

Where are the formulas that you want to hide? Presumably not in the blue cells.
Are you wanting all formulas in all sheets not visible - you only see formula if you select cells. So why not lock them out of selecting those cells?

[MattKoleczko]

The following issues;
Sub Lock excel doesn't like, so I changed it to Unlock Blue. That was fine except now it comes up with a run time error for...
cell.Locked = False

[kev_]

Ooops
Forgot to include line to unlock and re-lock each sheet
Put your password inside the quote marks in both lines
If there is no password then sht.Unprotect should also work

Please Login or Register  to view this content.

[MattKoleczko]

Unfortunately its still coming up with errors

[kev_]

I have tested it and it runs on my PC - so it must be something minor

Run this and look at the Immediate Window to see if it is failing from the first sheet or whether it's a particular sheet that is causing the problem.

To see the immediate window in VBA , Click on View, then "Immediate Window"

Please Login or Register  to view this content.

[kev_]

read previous post first
Here is my file for you to see the macro working
I have not set any passwords
The only cells that can be amended are on sheet2

[kev_]

Additionally Id like to hide all formulas so they're not visible to the end user

Please Login or Register  to view this content.