Forum Discussion
Unlock cells based on the color of text
Hi, I am trying to see if this is possible at all. I have some text in column B that is red, if so, I need the cells in columns C to Z to be unlocked. Is this possible and if so, how could I do th...
How to protect or lock cell values based on background color?
https://www.extendoffice.com/documents/excel/4136-excel-lock-cell-based-on-color.html
Any articles, templates, third-party products or information I have provided are for reference only. While I endeavor to keep the information up to date and correct as far as I can. I make no representations or warranties, express or implied, as to the completeness, correctness, reliability, suitability, or availability of any product or information, article, file, template, or related graphic on my posts in this forum. The trust you place in such information is therefore entirely at your own risk.
I would be happy to know if I could help.
Nikolino
I know I don't know anything (Socrates)
The selective sheet protection works with the following code (select area, the cells with the corresponding color are blocked).
------------------------------------------------
Private Sub cmdProtectSheet_Click ()
Dim cell As Range
For Each Cell In Selection
If cell.Interior.ColorIndex = 15 Then cell.Locked = True
'15 = light gray / 16 = dark gray / 6 = yellow / 19 = light yellow
'Cell.Locked = IIf (Cell.Interior.ColorIndex = 15, True, False)
'Cell.Locked = IIf (Cell.Interior.ColorIndex = 6, True, False)
'15 = light gray / 16 = dark gray / 6 = yellow / 19 = light yellow
Next cell
ActiveSheet.Protect Password: = "1234", DrawingObjects: = True, Contents: = True, UserInterfaceOnly: = True, Scenarios: = True
End Sub
-------------------------------------------------
The code for unlocking an area looks like this.
Private Sub cmdUnlockSheet_Click()
ActiveSheet.Unprotect Password:="1234"
Range("A1:AY497").Select
Range("AY497").Activate
Selection.Locked = False
Selection.FormulaHidden = False
Range("B65536").End(xlUp).Offset(2, 0).Select
End Sub
------------------------------------------------
Everything I have sent you so far should help you as much as possible as animation or support. that was the thought, if that doesn't help, please just ignore it.
Nikolino
I know I don't know anything (Socrates)
------------------------------------------------
Private Sub cmdProtectSheet_Click ()
Dim cell As Range
For Each Cell In Selection
If cell.Interior.ColorIndex = 15 Then cell.Locked = True
'15 = light gray / 16 = dark gray / 6 = yellow / 19 = light yellow
'Cell.Locked = IIf (Cell.Interior.ColorIndex = 15, True, False)
'Cell.Locked = IIf (Cell.Interior.ColorIndex = 6, True, False)
'15 = light gray / 16 = dark gray / 6 = yellow / 19 = light yellow
Next cell
ActiveSheet.Protect Password: = "1234", DrawingObjects: = True, Contents: = True, UserInterfaceOnly: = True, Scenarios: = True
End Sub
-------------------------------------------------
The code for unlocking an area looks like this.
Private Sub cmdUnlockSheet_Click()
ActiveSheet.Unprotect Password:="1234"
Range("A1:AY497").Select
Range("AY497").Activate
Selection.Locked = False
Selection.FormulaHidden = False
Range("B65536").End(xlUp).Offset(2, 0).Select
End Sub
------------------------------------------------
Everything I have sent you so far should help you as much as possible as animation or support. that was the thought, if that doesn't help, please just ignore it.
Nikolino
I know I don't know anything (Socrates)