Forum Discussion

jcote65's avatar
jcote65
Copper Contributor
Sep 30, 2020

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

Thanks

Jeanne

excel
Formulas and Functions

5 Replies

  • NikolinoDE's avatar
    NikolinoDE
    Platinum Contributor
    Sep 30, 2020

    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)

    • NikolinoDE's avatar
      NikolinoDE
      Platinum Contributor
      Sep 30, 2020
      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)
      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Sep 30, 2020

        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.

Resources