Excel Locking cells based on value in another cell

[Guest]

Hi all

My code locks another cell based on value in another cell. I got it working
just for 1 row but i would like to do it for the subsequent rows (E10, E11,
E12, E13...)... any help??



Private Sub Worksheet_Change(ByVal Target As Range)

If [E9] = "A" Then
ActiveSheet.Unprotect ("")
ActiveSheet.Range("G9:K9").Locked = True
ActiveSheet.Protect ("")
Else
ActiveSheet.Unprotect ("")
ActiveSheet.Range("G9:K9").Locked = False
ActiveSheet.Protect ("")
End If

End Sub



Thanks in advance

 

[Ken Johnson]

Hi all

My code locks another cell based on value in another cell. I got it working
just for 1 row but i would like to do it for the subsequent rows (E10, E11,
E12, E13...)... any help??

Private Sub Worksheet_Change(ByVal Target As Range)

If [E9] = "A" Then
ActiveSheet.Unprotect ("")
ActiveSheet.Range("G9:K9").Locked = True
ActiveSheet.Protect ("")
Else
ActiveSheet.Unprotect ("")
ActiveSheet.Range("G9:K9").Locked = False
ActiveSheet.Protect ("")
End If

End Sub

Thanks in advance

Something like this maybe...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lnLastRow As Long, rgCell As Range
lnLastRow = Cells(Rows.Count, "E").End(xlUp).Row
For Each rgCell In Range(Cells(9, "E"), Cells(lnLastRow, "E"))
If rgCell.Value = "A" Then
ActiveSheet.Unprotect ("")
ActiveSheet.Range("G" & rgCell.Row & ":K" & rgCell.Row).Locked = True
ActiveSheet.Protect ("")
Else
ActiveSheet.Unprotect ("")
ActiveSheet.Range("G" & rgCell.Row & ":K" & rgCell.Row).Locked = False
ActiveSheet.Protect ("")
End If
Next rgCell
End Sub

Ken Johnson

 

[Dave Peterson]

How about:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim RngToInspect As Range
Dim RngChanged As Range
Dim myCell As Range

With Me 'the worksheet with the code
Set RngToInspect = .Range("E9:E" & .Rows.Count)
End With

Set RngChanged = Intersect(Target, RngToInspect)

If RngChanged Is Nothing Then Exit Sub

On Error Resume Next
Me.Unprotect Password:=""
For Each myCell In RngChanged.Cells
If LCase(myCell.Value) = LCase("A") Then
myCell.Offset(0, 2).Resize(1, 5).Locked = True
Else
myCell.Offset(0, 2).Resize(1, 5).Locked = False
End If
Next myCell
Me.Protect Password:=""
On Error GoTo 0

End Sub

Just a note about: mycell.offset(0,2).resize(1,5)
mycell is in column E.
offset(0,2) "moves" it to column G
..resize(1,5) "expands" it to 1 row by 5 columns (G:K)

Hi all

My code locks another cell based on value in another cell. I got it working
just for 1 row but i would like to do it for the subsequent rows (E10, E11,
E12, E13...)... any help??

Private Sub Worksheet_Change(ByVal Target As Range)

If [E9] = "A" Then
ActiveSheet.Unprotect ("")
ActiveSheet.Range("G9:K9").Locked = True
ActiveSheet.Protect ("")
Else
ActiveSheet.Unprotect ("")
ActiveSheet.Range("G9:K9").Locked = False
ActiveSheet.Protect ("")
End If

End Sub

Thanks in advance