Avatar of Kim Dygert
Kim Dygert

asked on 

VBA to check cells and unprotect if necessary

I have a protected sheet where the user can input in certain cells only.

I would like VBA that will look for the text "other" in C15:D36, and if any of those cells contain any part of the word "other", I want it to unlock the Total Miles cell in that particular row.  For example:  if C15 OR D15 has any part of the text "other", I want code to unlock H15 so the user can input the total miles (overwriting the formula in that cell).  I found some code (see below) that I edited but not working as I expected.  I can't get 1 row to work let alone 22 rows.  Please help. (see also the file attached for screen shot) Thank you in advance.

Private Sub CellValueProtect(ByVal Target As Range)
    If Active.sheet.Range("C15:D15") = "Other" Then
        Active.sheet.Range("H15").Locked = False
    Else: Active.sheet.Range("H15").Locked = True
    End If
    If Active.sheet.Range("C16:D16") = "Other" Then
        Active.sheet.Range("H16").Locked = False
    Else: Active.sheet.Range("H16").Locked = True
    End If
    If Active.sheet.Range("C17:D17") = "Other" Then
        Active.sheet.Range("H17").Locked = False
    Else: Active.sheet.Range("H17").Locked = True
    End If
    If Active.sheet.Range("C18:D18") = "Other" Then
        Active.sheet.Range("H18").Locked = False
    Else: Active.sheet.Range("H18").Locked = True
    End If
           
End Sub
Screen-shot-of-sheet.JPG
VBAMicrosoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
Kim Dygert

8/22/2022 - Mon