Lock Unlock Cells Based On Format

  • I am looking for a way using VBA to lock and unlock multiple cells based on cell formatting (ex. to only allow edits to cells with a green border). Is it possible to write a macro that will accomplish this on command (not when the workbook is opened)?

  • Re: Locking/unlocking Cells Based On Format


    Assuming all of your cells are locked, this code will unlock any cell with a green (ColorIndex=10) border.


    Code
    1. Sub UnlockBasedOnBorderColor()
    2. Dim Cell As Range
    3. For Each Cell In ThisWorkbook.ActiveSheet.UsedRange
    4. Cell.Locked = (Cell.Borders.ColorIndex <> 10)
    5. Next
    6. End Sub


    Of course, you will need to add code to protect/unprotect the workbook in order for the cell protection to matter.

  • Re: Locking/unlocking Cells Based On Format


    Another way is via the Private Module of the Workheet;

    Code
    1. Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    2. Me.Protect Password:="Secret", UserInterFaceOnly:=True
    3. Target.Locked = Target.Cells.Borders.ColorIndex = 10
    4. End Sub
  • Re: Lock Unlock Cells Based On Format


    Hey thanks for the input guys, I will try these out and see how they work with what we are trying to accomplish![hr]*[/hr] Auto Merged Post;[dl]*[/dl]

    Quote from turtle44

    Of course, you will need to add code to protect/unprotect the workbook in order for the cell protection to matter.


    Right, this is another problem that I am running in to. Should I ask this in another thread or can I continue to ask in this one?