VBA Unlock Cells Depending on Cell Color

  • I plan on creating a large spreadsheet that will be placed on Citrix and accessed by many different users. Each person that uses the spreadsheet has different responsibilities and will need to fill in various pieces of information. I would like to be able to recognize who logs in and unlock only the cells for which that person is responsible. Each cell that needs an input would be color coated (ie: marketing would be green, sales would be blue, etc). I envisioned a code that, upon opening, would ask for a password and then unlock only the green cells if marketing logged on (so marketing doesn't have access to adjust sales fields). Is this possible? If so, what is the code for specifying an action if a cell is a certain color?

    Thanks for any assistance.


  • Re: VBA Unlock Cells Depending on Cell Color

    In your VBE under the help file for colorindex is this example:
    ColorIndex Property Example

    The following examples assume that you're using the default color palette.

    This example changes the font color in cell A1 on Sheet1 to red.

    Worksheets("Sheet1").Range("A1").Font.ColorIndex = 3
    This example sets the color of the major gridlines for the value axis in Chart1.

    With Charts("Chart1").Axes(xlValue)
        If .HasMajorGridlines Then
            .MajorGridlines.Border.ColorIndex = 5    'set color to blue
        End If
    End With

    This example sets the color of the chart area interior of Chart1 to red and sets the border color to blue.

    With Charts("Chart1").ChartArea
        .Interior.ColorIndex = 3
        .Border.ColorIndex = 5
    End With

    Here is code using If:

    If Range("B9").Interior.ColorIndex = 2 Then 'checks if cell color white
        Range("c9").Value = "Yes" 'if white then this cell says yes
            Range("c9").Value = "No" 'if not white then cell says no
    End If


    Few things in life are better than helping someone.

  • Re: VBA Unlock Cells Depending on Cell Color

    That last bit of code gets me on the right path. I just need to apply that to all cells on multiple tabs. Basically saying if any cell is white, then unlock.

    Thanks for the input.


  • Re: VBA Unlock Cells Depending on Cell Color

    Here is some more specific code to get you started.
    [vba]Sub FixLocks()
    Dim iColor As Integer, c As Range
    'figure out here what colorindex to unlock
    iColor = 4
    For Each c In Range("A1:G20") 'adjust as needed
    With c
    .Locked = Not (.Interior.ColorIndex = iColor)
    End With
    Next c
    End Sub[/vba]

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!