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.

Sub Unlock()
For Each sht In ActiveWorkbook.Sheets
    Set rng = sht.UsedRange
        For Each cell In rng
            If cell.Interior.ColorIndex = 20 Then
                cell.locked = False
            End If
        Next cell
Next sht
End Sub

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

Sub Color
MsgBox ActiveCell.Interior.ColorIndex
End Sub

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

Sub UnlockBlue()
For Each sht In ActiveWorkbook.Sheets
    sht.Unprotect Password:=""
    Set Rng = sht.UsedRange
        For Each cell In Rng
            If cell.Interior.ColorIndex = 20 Then
                cell.Locked = False
            End If
        Next cell
    sht.Protect Password:=""
Next sht
End Sub

[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"

Sub UnlockBlue()
For Each sht In ActiveWorkbook.Sheets
    Debug.Print sht.Name
    sht.Unprotect Password:=""
    Set Rng = sht.UsedRange
        Debug.Print sht.Name & " " & Rng.Address(0, 0)
        For Each cell In Rng
            Debug.Print cell.Address(0, 0)
            If cell.Interior.ColorIndex = 20 Then
                cell.Locked = False
            End If
        Next cell
    sht.Protect Password:=""
Next sht
End Sub

[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

For Each sht In ActiveWorkbook.Sheets
    sht.Unprotect Password:=""
    Set Rng = sht.UsedRange
        For Each cell In Rng
            cell.FormulaHidden = True
        Next cell
    sht.Protect Password:=""
Next sht