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.


    Chris

    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.

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

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


    Here is code using If:


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


    HTH

    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.


    Chris

    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
    ActiveSheet.Unprotect
    For Each c In Range("A1:G20") 'adjust as needed
    With c
    .Locked = Not (.Interior.ColorIndex = iColor)
    End With
    Next c
    ActiveSheet.Protect
    End Sub[/vba]

Participate now!

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

Register Yourself Login