Unlock cells based on the color of text

New Contributor


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 this?



5 Replies


Is the font color set manually, or is it based on the value of one or more cells?

@Hans Vogelaar  - it's set manually


Then it will be hard, if not impossible, to have Excel do this automatically. There is no event that fires when you change the text color or fill color of a cell.



How to protect or lock cell values based on background color?



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.



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

I know I don't know anything (Socrates)