Hi everyone,
I have a code running that locks and unlocks cells in a row based on the values in Column A and B in that row.
So for example when you fill in a value in Row 3 column A or B, checks if the values meet the criteria:
Row 3 Column A is "In Process" and Row 3 Column B is not empty
If the criteria is met, Row 3 Columns E and onwards become unlocked.
If the criteria is not met, Row 3 Columns E and onwards become locked.
This is the code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long
Dim rng As Range
If Not Intersect(Target, Range("a:a")) Is Nothing Or Not Intersect(Target, Range("b:b")) Is Nothing Then
ActiveSheet.Unprotect ""
i = Target.Row
Set rng = Range(Cells(i, 5), Cells(i, 130))
If Cells(i, 1) = "In Process" And Not IsEmpty(Cells(i, 2)) Then
rng.Locked = False
Else
rng.Locked = True
End If
ActiveSheet.Protect "", _
AllowFiltering:=True
End If
End Sub
It works when you edit one cell at a time, but if you use a copy-down to fill in cells A or B on multiple rows in bulk, it only performs the code on the first edited row.
I am looking to either:
a) rewrite the code so it is capable of checking more than 1 row at once
OR
b) make a "recalculate lock/unlock on all rows" button with a macro that goes through each row, edits a value in column A or B in each row, thus triggering the original code to run
Any ideas, geniuses?
I have a code running that locks and unlocks cells in a row based on the values in Column A and B in that row.
So for example when you fill in a value in Row 3 column A or B, checks if the values meet the criteria:
Row 3 Column A is "In Process" and Row 3 Column B is not empty
If the criteria is met, Row 3 Columns E and onwards become unlocked.
If the criteria is not met, Row 3 Columns E and onwards become locked.
This is the code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long
Dim rng As Range
If Not Intersect(Target, Range("a:a")) Is Nothing Or Not Intersect(Target, Range("b:b")) Is Nothing Then
ActiveSheet.Unprotect ""
i = Target.Row
Set rng = Range(Cells(i, 5), Cells(i, 130))
If Cells(i, 1) = "In Process" And Not IsEmpty(Cells(i, 2)) Then
rng.Locked = False
Else
rng.Locked = True
End If
ActiveSheet.Protect "", _
AllowFiltering:=True
End If
End Sub
It works when you edit one cell at a time, but if you use a copy-down to fill in cells A or B on multiple rows in bulk, it only performs the code on the first edited row.
I am looking to either:
a) rewrite the code so it is capable of checking more than 1 row at once
OR
b) make a "recalculate lock/unlock on all rows" button with a macro that goes through each row, edits a value in column A or B in each row, thus triggering the original code to run
Any ideas, geniuses?