Lock Cells

[Neil Pearce]

Dear all,

I wonder if someone could help?

I have a range of cells, B2:AY51, I wish to select all the range and 'lock'
them for protection. Then I'd like to 'unlock' only those that are red.


Thanking-you in advance,

Kind regards,

Neil

 

[Gord Dibben]

What about all the other cells on the worksheet? Locked or unlocked?

Are the red cells within the B2:AY51 range or?

How did those cells get to be colored red?


Gord Dibben MS Excel MVP

 

[Neil Pearce]

Good evening Gorden,

The range is B1:AY51. The red cells do lie in the this range, there may be
red cellsoutside of this too bu they do not require locking necessarily.

Teh range has been copied and pasted from another workbook, i.e it would
take an age to indiviually select all the red cells.

Other cells on the worksheet are both locked and unlocked are requried not
to be altered.

I would hazzard a guess that the answer would be along the lines of:
(although I know not how to write it, or any code as yet)

Sub (Unprotect Red)
Define Range
For each cell in range
If red unlock
If not red lock
End


Kindest of regards,

Neil

 

[Gord Dibben]

Try this worksheet_activate code.

When you select the worksheet, the event code runs.

Private Sub Worksheet_Activate()
Dim rng As Range
With Me
.Unprotect Password:="justme"
.Range("B2:AY51").Cells.Locked = True
For Each rng In .Range("B2:AY51")
If rng.Interior.ColorIndex = 3 Then
rng.Locked = False
End If
Next rng
.Protect Password:="justme"
End With
End Sub

Right-click on the sheet tab and "View code". Copy/paste the code into that
worksheet module.

Alt + q to return to the Excel window.

Switch to another sheet then back to this sheet.

Test the "lockedness" of the red cells vs non-red cells by typing in them.


Gord