troubleshooting Question

VBA to check cells and unprotect if necessary

Avatar of Kim Dygert
Kim Dygert asked on
VBAMicrosoft ExcelMicrosoft Office
12 Comments1 Solution80 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 12 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 12 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros