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
    Sub UnlockBasedOnBorderColor()
   Dim Cell As Range
   For Each Cell In ThisWorkbook.ActiveSheet.UsedRange
      Cell.Locked = (Cell.Borders.ColorIndex <> 10)
   Next
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
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Me.Protect Password:="Secret", UserInterFaceOnly:=True
    Target.Locked = Target.Cells.Borders.ColorIndex = 10
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?

Participate now!

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

Register Yourself Login