Lock/Unlock Cells Based On Validation Lists

  • Hi, I really need your help, i have to lock some cells in excel that should only be available (or open) if the user changes another cell, so i'll try to explain the problem in detail. Please remember i'm using excel 2010.

    In F11 i have a validation list, where the user can choose "On Leave" and "Active", if F11 is showing on leave i want all the cells from J10 to P11 to be locked (Greyed) so no input can be captured on those cells unless the user changes the cell in F11 to "Active", in that case the cells from J10 to P11 must be unlocked.

    Hope this makes sense, but also keep in mind that i have to do that not only for f11 but f12, f13, f14 and so on so forth, i will am attaching a copy of the spreadsheet so you can see what i mean with the list.

    I would really appreciate your help with this.


  • Re: Lock/Unlock Cells Based On Validation Lists

    Hi Ana,

    As you wrote "remember I'm using Excel 2010" I don't know if this is what you need (using Excel 2003 version).



  • Re: Lock/Unlock Cells Based On Validation Lists

    VBA is subject to users choosing to enable macros - which they can elect not to do and thus defeat the code.

    First - there is no validation list in cell F11 (or other cells) and the actual cell containing the value is F10 - you have the cells merged (bad idea - causes problems).
    However, you can accomplish this without the use of VBA by using Conditional Formatting and Data Validation in conjunction with a helper cell.

    Using column-Z as the help column, you could enter this formula: =F10="On Leave"
    Which will return TRUE or FALSE; TRUE = F10 contains "On Leave"; FALSE = F10 does not contain it

    Set cells J10:P11 to use Conditional Formatting, formula option, using this formula; =$Z10=TRUE
    Select a gray fill color
    Cells J10:P11 will be colored gray if Z10 = TRUE due to F10 containing "On Leave"

    Next . . .
    Apply Data Validation to cells J10:P11, using the Custom option and use this formula: =$Z10=FALSE
    Set the Error Alert style to Stop and create a custom message if desired.
    When Z10 = False it means cells F10 contains "Active" so users can make entries. If Z10 = False then cell F10 contains "On Leave" and Data Validation will prevent users from making entries.

  • Re: Lock/Unlock Cells Based On Validation Lists

    I applied this to my spreadsheet, but somehow when the cells are supposed to be cleared of the conditional formatting, only the top cell clears and the rest stays black (color I used).

    I did all of it just the same.



    then for range =$A$3:$E$24
    =G1=FALSE then it colors everything black. then I clicked the box Stop if True. when the cell a1 has data, it only clears a3 and the rest stays black

  • Re: Lock/Unlock Cells Based On Validation Lists


    Welcome to Ozgrid.

    However, posting your question in a thread started by someone else is against Forum Rules (see link in my signature) and is known as thread hijacking.
    Always start your own thread and, if it helps, you can provide a link back to this (or any other) post you think helps clarify your thread.

    Please start your own thread and be sure to create a thread title that concisely and accurately describes YOUR thread content and is "search friendly", meaning YOUR title used as the search terms will yield good results.


Participate now!

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