Unlock cells based on the color of text

%3CLINGO-SUB%20id%3D%22lingo-sub-1728618%22%20slang%3D%22en-US%22%3EUnlock%20cells%20based%20on%20the%20color%20of%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1728618%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20see%20if%20this%20is%20possible%20at%20all.%26nbsp%3B%20I%20have%20some%20text%20in%20column%20B%20that%20is%20red%2C%20if%20so%2C%20I%20need%20the%20cells%20in%20columns%20C%20to%20Z%20to%20be%20unlocked.%3C%2FP%3E%3CP%3EIs%20this%20possible%20and%20if%20so%2C%20how%20could%20I%20do%20this%3F%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3EJeanne%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1728618%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1728659%22%20slang%3D%22en-US%22%3ERe%3A%20Unlock%20cells%20based%20on%20the%20color%20of%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1728659%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F814727%22%20target%3D%22_blank%22%3E%40jcote65%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIs%20the%20font%20color%20set%20manually%2C%20or%20is%20it%20based%20on%20the%20value%20of%20one%20or%20more%20cells%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1728802%22%20slang%3D%22en-US%22%3ERe%3A%20Unlock%20cells%20based%20on%20the%20color%20of%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1728802%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%20-%20it's%20set%20manually%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

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

Thanks

Jeanne

5 Replies
Highlighted

@jcote65 

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

Highlighted

@Hans Vogelaar  - it's set manually

Highlighted

@jcote65 

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.

Highlighted

@jcote65 

 

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)

Highlighted
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)