How to apply VBA in every row

Hi All,

im trying to make some sort of a daily report where in column "R" has a drop down list of its status.

i.e. in row 6.
once the work is finished, the staff will choose "COMPLETED" in column R6.
What i would like to happen is that whenever they chose that word, selected cells in row6 will be locked or protected.

i've manage to create VBA but it only applies to row6 only. would like apply the same rule in every row.

please advise.. VBA i created is here...

----------------------------------------------------------------------------

Private Sub Worksheet_Change(ByVal Target As Range)
     
    ActiveSheet.Unprotect Password:="11123"
     
    If Range("R6") = "COMPLETED" Then
        Range("B6:Q6").Locked = True
        Range("S6:T6").Locked = True
    Else
        Range("B6:Q6").Locked = False
        Range("S6:T6").Locked = False
    End If
     
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="11123"
     
End Sub

---------------------------------------------------------------------------------------------

Thank you in Advance !!!

Hi and welcome to the forum. Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

To do this highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

Since this is your first post I'll add the code tags for you but please remember for the future.

Hi

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect (Target,Range("R1:R1000")) Is Nothing Then     

    ActiveSheet.Unprotect Password:="11123"
     
    If Target.Value = "COMPLETED" Then
        Range("B" & Target.Row & ":Q" & Target.Row).Locked = True
        Range("S" & Target.row & ":T" & Target.Row).Locked = True
    Else
        Range("B" & Target.Row & ":Q" & Target.Row).Locked = False
        Range("S" & Target.row & ":T" & Target.Row).Locked = False
    End If
     
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="11123"
End If     
End Sub