VBA locking the same row based on the same row another cell

[mrcaglayan]

I found this code, but here K:P range is locked when J is anything. I want this code to work ( lock the row ) when the J is a specific text value. Any help much appreciated. Thank you.

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Const PWORD = "PW"

With Target.Parent
If Target.Column = 10 Then
.Unprotect PWORD
Intersect(Target.EntireRow, .Range("K:P")).Locked = True
.Protect PWORD
End If
End With
End Sub

 

[Osvaldo Palmeiro]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
 Const PWORD = "PW"
 If Target.Column <> 10 Or Target.Value <> "put the specific text here" Then Exit Sub
 Me.Unprotect PWORD
 Cells(Target.Row, "K").Resize(, 6).Locked = True
 Me.Protect PWORD
End Sub

 

[mrcaglayan]

it worked like a charm. Thank you very muchhhh ^^

Is it possible to add another target.column to have 2 cells conditions to lock the target.Row . I tried this but didn't work.

Private Sub Worksheet_Change(ByVal Target As Range)
Const PWORD = "PW"
If Target.Column <> 10 Or Target.Value <> "abcd" And Target.Column <> 9 Or Target.Value <> "efgh" Then Exit Sub
Me.Unprotect PWORD
Cells(Target.Row, "K").Resize(, 6).Locked = True
Me.Protect PWORD
End Sub

 

[Osvaldo Palmeiro]

Hi.
I assumed that you want K:P will be blocked only in case that cell change occurs in column I or in column J, and if I=Text1 AND J=Text2.

Private Sub Worksheet_Change(ByVal Target As Range)
 Const PWORD = "PW"
 If Target.Column < 9 Or Target.Column > 10 Then Exit Sub
 Me.Unprotect PWORD
 Cells(Target.Row, "K").Resize(, 6).Locked = False
 If Cells(Target.Row, "I") = "efgh" And Cells(Target.Row, "J") = "abcd" Then
  Cells(Target.Row, "K").Resize(, 6).Locked = True
 End If
 Me.Protect PWORD
End Sub

 

[mrcaglayan]

Hi.
I assumed that you want K:P will be blocked only in case that cell change occurs in column I or in column J, and if I=Text1 AND J=Text2.

Private Sub Worksheet_Change(ByVal Target As Range)
 Const PWORD = "PW"
 If Target.Column < 9 Or Target.Column > 10 Then Exit Sub
 Me.Unprotect PWORD
 Cells(Target.Row, "K").Resize(, 6).Locked = False
 If Cells(Target.Row, "I") = "efgh" And Cells(Target.Row, "J") = "abcd" Then
  Cells(Target.Row, "K").Resize(, 6).Locked = True
 End If
 Me.Protect PWORD
End Sub

Hi Sir.
That's what exactly i was trying to figure out. God bless you. You are the best ^^

 

[Osvaldo Palmeiro]

Hi, Sir.
Glad it helped and thanks for the kindly words and feedback.
God bless you too, and Happy 2024.

 

[mrcaglayan]

Hi sir again. Sir i was struggling with having those codes working. I did 3 different combinations with the code i asked befoe. Is there a way to combine those to have them working.

Private Sub Worksheet_Change(ByVal Target As Range)
 Const PWORD = "PW"
 If Target.Column <> 2 Or Target.Value <> "YENI KAYIT" Then Exit Sub
 Me.Unprotect PWORD
End Sub

 If Target.Column <> 2 Or Target.Value <> "KAYIT YENILEME" Then Exit Sub
 Me.Unprotect PWORD
 Cells(Target.Row, "I").Resize(, 2).Locked = False
 Me.Protect PWORD
End Sub

 If Target.Column < 18 Or Target.Column > 19 Then Exit Sub
 Me.Unprotect PWORD
 Cells(Target.Row, "B").Resize(, 17).Locked = False
 If Cells(Target.Row, "R") = "Save" And Cells(Target.Row, "S") = "Print" Then
  Cells(Target.Row, "K").Resize(, 6).Locked = True
 End If
 Me.Protect PWORD
End Sub

 

[mrcaglayan]

Private Sub Worksheet_Change(ByVal Target As Range)
 Const PWORD = "PW"
 If Target.Column <> 2 Or Target.Value <> "YENI KAYIT" Then Exit Sub
 Me.Unprotect PWORD
 Cells(Target.Row, "I").Resize(, 6).Locked = False
 Me.Protect PWORD
End Sub

 If Target.Column <> 2 Or Target.Value <> "KAYIT YENILEME" Then Exit Sub
 Me.Unprotect PWORD
 Cells(Target.Row, "I").Resize(, 1).Locked = False
 Me.Protect PWORD
End Sub

 If Target.Column < 18 Or Target.Column > 19 Then Exit Sub
 Me.Unprotect PWORD
 Cells(Target.Row, "B").Resize(, 17).Locked = False
 If Cells(Target.Row, "R") = "Save" And Cells(Target.Row, "S") = "Print" Then
  Cells(Target.Row, "K").Resize(, 6).Locked = True
 End If
 Me.Protect PWORD
End Sub

 

[mrcaglayan]

I couldn't find the edit option for posted message. So I posted the code again above. Sorry for the spam. :/

 

[Osvaldo Palmeiro]

Hi.
Here is the code that does what I understood from your last code.
If it doesn't, then please explain in plain English what you want to do.

Private Sub Worksheet_Change(ByVal Target As Range)
 Const PWORD = "PW"
 Me.Unprotect PWORD
 If Target.Column = 2 Then
  Cells(Target.Row, "I").Resize(, 6).Locked = False
  If Target.Value = "YENI KAYIT" Then Cells(Target.Row, "I").Resize(, 6).Locked = True
  If Target.Value = "KAYIT YENILEME" Then Cells(Target.Row, "I").Locked = True
 ElseIf Target.Column >= 18 And Target.Column <= 19 Then
  Cells(Target.Row, "B").Resize(, 17).Locked = False
  If Cells(Target.Row, "R") = "Save" And Cells(Target.Row, "S") = "Print" Then
   Cells(Target.Row, "K").Resize(, 6).Locked = True
  End If
 End If
 Me.Protect PWORD
End Sub