I’m in the process of creating an Excell version of an OpenOffice worksheet. I need two range (A7:D10 and A12:D15) to unlock themselves as to allow manual input when cell K6 or K7 indicate the value “Blank”. I was able to do this in openoffice with conditinal formating which did not translate to Excel. I tried using a Macro in Excell but get Error 1004 as soon as I lock the sheet. Is there anyway I can achieve this, either through script or formula? The sheet need to be protected due to my companys policy. Thanks.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Range("$K$7") = "Blank" Then
        Range("A12:D15").Locked = False
    ElseIf Range("$K$7") = "" Then
        Range("A12:D15").Locked = True
    End If
End Sub

It works fine as long as the sheets stays unprotected. As soon as I protect is, I get Error 1004, unable to define the property Locked of Range class.