Quick way to protect/unprotect cells

This assumes that the yellow cells are the vbYellow color.

Sub LockYellow()
Dim cel As Range

' Choose the cells that you DON'T want protected
For Each cel In ActiveSheet.UsedRange.Cells
    If cel.Interior.Color <> vbYellow Then
        cel.Locked = False
    End If
Next

' Protect all the rest
Worksheets("Sheet1").Protect Password:="Secret", UserInterfaceOnly:=True
End Sub

Select allOpen in new window

Sub UnlockAll()
Worksheets("Sheet1").Unprotect Password:="Secret" ', UserInterfaceOnly:=True
End Sub

Select allOpen in new window

29222256.xlsm

Sorry Think I confused you. What im looking for is to be able to select the whole sheet and the cells that are yellow colour I need UNPROTECTED. or am I missing a bit I need to add to the macro

If you want to protect the cell, you can set cell's Locked property to be True. And Locked property to be False, if you want to unprotect the cell.

After that you can then protect the sheet using the code below.

Sub Protect_Workbook(Para_Message As String)
    Application.ScreenUpdating = True
    Worksheets("Main Sheet").Protect Password:="12345"
End Sub

Select allOpen in new window

Here is the way to unprotect the sheet.


Sub Unprotect_Workbook(Para_Message As String)
    Application.ScreenUpdating = False
    Worksheets("Main Sheet").Unprotect Password:="12345"
End Sub

Select allOpen in new window

Chnge line 6 from

If cel.Interior.Color <> vbYellow Then

Select allOpen in new window

to

If cel.Interior.Color = vbYellow Then

Select allOpen in new window

Sorry changed that and now comes up with run time error 424.

Did you include the Dim cel As Range line in my code?

Please supply a small sample workbook.

This is the actual workbook and im looking at locking everything apart from yellow cells on Data Input sheet. Copy of Copy of CubicSheet V1.3.xlsm

I’m glad I was able to help.

If you expand the “Full Biography" section of my profile you’ll find links to some articles I’ve written that may interest you.

Marty - Microsoft MVP 2009 to 2017
              Experts Exchange Most Valuable Expert (MVE) 2015, 2017
              Experts Exchange Distinguished Expert in Excel 2018
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2020
              Experts Exchange Top Expert VBA 2018 to 2020