Lock a range of cells based off a value of another cell and duplicate for multiple rows

Hi,

I have been having trouble with this problem.

I am creating an assessment and when the value in Column K is equal to "No" I would like for Columns L through T and X & Y to be locked. I have been able to get it to work for one row but I have 500 rows and am hoping there is a way to not have to write out the macro 500+ times.

This is what I have for 1 row and range right now:

Private Sub Worksheet_Change(ByVal Target As Range)

If Range("K3") = "Yes" Then
Range("L3:T3").Locked = False
ElseIf Range("K3") = "No" Then
Range("L3:T3").Locked = True


End If

End Sub

I need it for all the rows in my sheet. For an example: If K3 is "no" then only L3:T3 and X3:Y:3 will be locked and if K4 is "Yes" then L4:T4 and X4:Y:4 are unlocked but if K5 is "No" then L5:T5 and X5:Y:5 will be locked, and so forth...

Any help is appreciated!

Thank you

This worked, thank you!

It is selected through a dropdown from a data validation

I am not having a different problem with this now. I have a button that clears all the user inputted data, however it is giving me a mismatch error now. (I think it may be because the cells are locked?) Any idea of how to get around that as well.

This is the code I have for that button:

Sub CommandButton1_Click()

If MsgBox("This will clear all user inputted data, these changes cannot be undone. Do you wish to proceed?", vbYesNo + vbQuestion) = vbYes Then

Worksheets("02_Risk Assessment").Unprotect Password:="abc"
Worksheets("04_Control Assessment").Unprotect Password:="abc"

Worksheets("02_Risk Assessment").Range("E3:J50").ClearContents
Worksheets("02_Risk Assessment").Range("L3:L50").ClearContents
Worksheets("04_Control Assessment").Range("K3:T480").ClearContents
Worksheets("04_Control Assessment").Range("X3:Y480").ClearContents

Worksheets("02_Risk Assessment").Protect Password:="abc"
Worksheets("04_Control Assessment").Protect Password:="abc"


End If

End Sub

The formula that you gave previously is in the "04_Control Assessment" sheet.

Thank you!

Attached is the spreadsheet. Thank you

Hi,

Where do I put this code. I tried putting it into the button on the information sheet and that by passes the code but does not perform the clear to the 04 Assessment tab.

Code:

Please Login or Register  to view this content.

That was then giving me Error 1004 saying the sheet is protected event tho the code is there to first unprotect.

However, I found a way to make it work by also adding it into the button, the code for the 04 Assessment and button are below.

Thank you again for your help!

Please Login or Register  to view this content.

Please Login or Register  to view this content.