VBA Lock Cell in row if yes in selected

[FN1234]

Hello, I have the following code to lock the entire row, if the cell selects Yes, but how can I just lock the row from column a to Cd if cell.value is yes.

I also then want to say if cell.value="No" , unlock the row and if it's "" do nothing to the row.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

For Each cell In Range("CE7:CE357")
If cell.Value = "Yes" Then
ActiveSheet.Unprotect "Password"

cell.EntireRow.Locked = True

ActiveSheet.Protect "Password"

End If

Next cell
End Sub

 

[Joe4]

Welcome to the Board!

Firstly, I think you are using the wrong Event Procedure. The way you have written, any time you move in your workbook (select a different cell), it is going to evaluate all 351 rows! Every time!
And I don't think you want your protect/unprotect lines in the loop, as that will unlock/lock 351 times each time it runs too! You should only unlock/lock it once per time the procedure it called.
These things are a bit overkill and might slow your workbook down.

I believe that you really only need it to run when you change a value in CE7:CE57, and only need to check the row(s) you are changing. So a Worksheet_Change event procedure is probably more appropriate, like this:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim isect As Range
    Dim cell As Range
    Dim rw As Long
    
    Set isect = Intersect(Target, Range("CE7:CE357"))
    
    If Not isect Is Nothing Then
        ActiveSheet.Unprotect "Password"
        For Each cell In isect
            rw = cell.Row
            Select Case UCase(cell.Value)
                Case "YES"
                    Range(Cells(rw, "A"), Cells(rw, "CD")).Locked = True
                Case "NO"
                    Range(Cells(rw, "A"), Cells(rw, "CD")).Locked = False
            End Select
        Next cell
        ActiveSheet.Protect "Password"
    End If
    
End Sub

 

[FN1234]

Wow thanks so much! This works perfectly! And my sheet is so much faster now too!

 

[Joe4]

You are welcome! Glad I was able to help!

Hope it all makes sense (let me know if you have any specific questions about the code).

 

[FN1234]

What is the best way to learn VBA?

I never learned in a traditional setting, just doing it as I go for work, but I feel like my code isn't always the most efficient.

 

[Joe4]

What is the best way to learn VBA?

Well, it depends on the person. Some are find at learning from books, others are more visual and like demonstrations. MrExcel has a lot of books in their bookstore for various different levels (https://www.mrexcel.com/store/).

Hiker95 has also put together a very extensive list of on-line sources:
https://www.mrexcel.com/forum/lounge-v-2-0/1031751-two-novice-questions-post4952119.html#post4952119

And of course, hanging around here and reading through various threads that interest you will help augment your knowledge and skills!