Results 1 to 2 of 2

Thread: Locking Cells If Certain Values Is Selected

  1. #1

    Locking Cells If Certain Values Is Selected

    I wish to lock and color marked cells if selected "Not Must", otherwise to unlock and uncolor.
    Will appreciate any help with the the best way to do that.

  2. #2
    The solution!
    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
        Dim ws As Worksheet
        Dim MustNot As String
        Set ws = ActiveSheet
        MustNot = ws.Range("B4").Value ' & PatientRow).Value
        ' unprotect the sheet so that we can modify locked settings
        ws.Range("C4:N4").Cells.Locked = False
        ' lock row
        Range("C4:N4").Cells.Locked = True
        Range("C4:N4").Interior.ColorIndex = 48
        ' unlock the row depending on answer
        If MustNot = "Must" Then
            Range("C4:N4").Cells.Locked = False
            Range("C4:N4").Interior.ColorIndex = 27
        End If
        ' protect the sheet again to activate the locked cells
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts