Excel Locking cells based on value in another cell
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 |
Excel Locking cells based on value in another cell
On Oct 11, 5:56 am, Kelson wrote:
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 |
Excel Locking cells based on value in another cell
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) Kelson wrote: 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 -- Dave Peterson |
All times are GMT +1. The time now is 06:50 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com