Forum Discussion
Solved
Using protected cells
I am trying to accomplish the following: I have a document where depending one what is selected in A2 - cells are highlighted, based on conditional formatting, to either stay the same or be graye...
In the first place, ActiveSheet.Range("C2") will never be Nothing - it is an object with lots of properties. So the code below won't be executed.
In the second place, locking cells only works if the sheet as a whole is protected. The code will have to unprotect the sheet, lock or unlock some cells, then protect the sheet again. You can use
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Range("C2"), Target) Is Nothing Then Me.Unprotect Range("B43:J49").Locked = (UCase(Range("C2").Value) = "X") Me.Protect End If End SubSee the small sample workbook attached to this reply (you'll have to allow macros).
Yes, I was doing some more research and it seems like that is the only way
You can create a Worksheet_Change event procedure in the worksheet module that checks whether A2 has been changed. If so:
- Unprotect the sheet.
- Unlock cells that should be editable depending on the value of A2.
- Lock cells that should not be editable depending on the value of A2.
- Protect the sheet again