Specific Text Enter Entire Row should protect

[nithin shettigar]

Please help me out with VBA code, if I enter “ok” any ware betweenE1:E500 entire row should be protected.
Eg – If I enter ok in “E2” then “A2:D2” should be protected

[TABLE="width: 377"]
<colgroup><col width="26" style="width: 20pt; mso-width-source: userset; mso-width-alt: 950;"><colgroup><col width="84" style="width: 63pt; mso-width-source: userset; mso-width-alt: 3072;"><colgroup><col width="64" style="width: 48pt;"><colgroup><col width="75" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2742;" span="2"><colgroup><col width="64" style="width: 48pt;"><colgroup><col width="115" style="width: 86pt; mso-width-source: userset; mso-width-alt: 4205;"><tbody>[TR]
[TD="width: 26, bgcolor: transparent"] [/TD]
[TD="width: 84, bgcolor: transparent, align: center"]A[/TD]
[TD="width: 64, bgcolor: transparent, align: center"]B[/TD]
[TD="width: 75, bgcolor: transparent, align: center"]C[/TD]
[TD="width: 75, bgcolor: transparent, align: center"]D[/TD]
[TD="width: 64, bgcolor: transparent, align: center"]E[/TD]
[TD="width: 115, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: center"]1[/TD]
[TD="bgcolor: transparent, align: center"]Name[/TD]
[TD="bgcolor: transparent, align: center"]Place [/TD]
[TD="bgcolor: transparent, align: center"]Date[/TD]
[TD="bgcolor: transparent, align: center"]Time[/TD]
[TD="bgcolor: transparent, align: center"]Remark[/TD]
[TD="bgcolor: transparent, align: center"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: center"]2[/TD]
[TD="bgcolor: transparent, align: center"]Suhan[/TD]
[TD="bgcolor: transparent, align: center"]Dubai[/TD]
[TD="bgcolor: transparent, align: center"]15/01/2019[/TD]
[TD="bgcolor: transparent, align: center"]11:57 PM[/TD]
[TD="bgcolor: transparent, align: center"]ok[/TD]
[TD="bgcolor: transparent, align: center"]protect row[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: center"]3[/TD]
[TD="bgcolor: transparent, align: center"]Nithin[/TD]
[TD="bgcolor: transparent, align: center"]Dubai[/TD]
[TD="bgcolor: transparent, align: center"] [/TD]
[TD="bgcolor: transparent, align: center"] [/TD]
[TD="bgcolor: transparent, align: center"]
[/TD]
[TD="bgcolor: transparent, align: center"]unprotect row[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: center"]4[/TD]
[TD="bgcolor: transparent, align: center"]Yash[/TD]
[TD="bgcolor: transparent, align: center"]Dubai[/TD]
[TD="bgcolor: transparent, align: center"]15/01/2019[/TD]
[TD="bgcolor: transparent, align: center"]12:57 PM[/TD]
[TD="bgcolor: transparent, align: center"]ok[/TD]
[TD="bgcolor: transparent, align: center"]protect row[/TD]
[/TR]
</tbody>[/TABLE]

 

[Yongle]

Do you want VBA to react when you change the values in column E manually?

Selecting a different cell in column E toggles its value between "OK" and empty string ""

Code below assumes that cells in column E are not locked
Place in sheet module and amend the password

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range, pWord As String
    pWord = "[I][COLOR=#006400]XXX[/COLOR][/I]"
    With Target
        If .Column = 5 And .Row > 1 Then
            Set rng = .Offset(, -4).Resize(, 4)
            Me.Unprotect pWord
            If .Value = "OK" Then rng.Locked = True Else rng.Locked = False
            Me.Protect pWord
        End If
    End With
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    With Target
        If .CountLarge > 1 Then Exit Sub
        If .Column = 5 And .Row > 1 Then
            If .Value = "OK" Then .Value = "" Else .Value = "OK"
        End If
    End With
End Sub

To place code in sheet module
right-click on sheet tab \ select View Code \ paste code in code window \ go back to Excel with {Alt}{F11}

 

[nithin shettigar]

Thank you for your reply,

Please note column “E” is already locked, I want to run thiscode upon closing my workbook .

 

[Rick Rothstein]

Thank you for your reply,

Please note column “E” is already locked, I want to run thiscode upon closing my workbook .

Wouldn't it be better to protect the cells when an attempt is made to change them? Think about it... what if the user made a lot of changes, some on rows that were not protected and others on rows that were protected. It would be awkward to try and put the original data back on the protected rows and, if you decided not to allow that, then all the legitimate changes would be lost. I would suggest you try the below Change event code and see how well it protects the cells immediately when the change is attempted.

[table="width: 500"]
[tr]
	[td]Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Cell As Range
  On Error GoTo OutOfRange
  For Each Cell In Intersect(Target, Columns("A:D"))
    If UCase(Cells(Target.Row, "E").Value) = "OK" Then
      MsgBox "Sorry, this row is protected so you cannot make to Columns A thru D on this row."
      Application.EnableEvents = False
      Application.Undo
      Application.EnableEvents = True
      Exit For
    End If
  Next
OutOfRange:
End Sub[/td]
[/tr]
[/table]

HOW TO INSTALL Event Code
------------------------------------
If you are new to event code procedures, they are easy to install. To install it, right-click the name tab at the bottom of the worksheet that is to have the functionality to be provided by the event code and select "View Code" from the popup menu that appears. This will open up the code window for that worksheet. Copy/Paste the event code into that code window. That's it... the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself. Note... if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.