I have a tracker that has an Approval column. Once a row of Data has been Approved or Denied I would to lock certain cells in that row. When the Approval column cell is blank I want the cells in that row to be unlocked.
Here is what I have so far but am running into Run-time error '91' Object Variable or with block variable not set.
Here is what I have so far but am running into Run-time error '91' Object Variable or with block variable not set.
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rInt As Range
Dim rCell As Range
Dim tCell As Range
Set rInt = Intersect(Target, Range("n:n"))
If rInt = Sheets("Data").Range("M4") Then
Worksheets("CL 2 Request").Unprotect ("Secret")
For Each rCell In rInt
Set tCell = rCell.Offset(0, 0 - 1)
tCell.Locked = True
Next
For Each rCell In rInt
Set tCell = rCell.Offset(0, 0 - 2)
tCell.Locked = True
Next
For Each rCell In rInt
Set tCell = rCell.Offset(0, 0 - 3)
tCell.Locked = True
Next
For Each rCell In rInt
Set tCell = rCell.Offset(0, 0 - 4)
tCell.Locked = True
Next
For Each rCell In rInt
Set tCell = rCell.Offset(0, 0 - 5)
tCell.Locked = True
Next
For Each rCell In rInt
Set tCell = rCell.Offset(0, 0 - 6)
tCell.Locked = True
Next
For Each rCell In rInt
Set tCell = rCell.Offset(0, 0 - 7)
tCell.Locked = True
Next
For Each rCell In rInt
Set tCell = rCell.Offset(0, 0 - 8)
tCell.Locked = True
Next
For Each rCell In rInt
Set tCell = rCell.Offset(0, 0 - 9)
tCell.Locked = True
Next
For Each rCell In rInt
Set tCell = rCell.Offset(0, 0 - 10)
tCell.Locked = True
Next
For Each rCell In rInt
Set tCell = rCell.Offset(0, 0 - 11)
tCell.Locked = True
Next
For Each rCell In rInt
Set tCell = rCell.Offset(0, 0 - 12)
tCell.Locked = True
Next
For Each rCell In rInt
Set tCell = rCell.Offset(0, 0 - 13)
tCell.Locked = True
Next
Worksheets("CL 2 Request").Protect Password:="Secret", DrawingObjects:=False, contents:=True, Scenarios:=False, AllowFormattingCells:=True, AllowSorting:=True, AllowFiltering:=True
ElseIf rInt = Sheets("Data").Range("M5") Then
Worksheets("CL 2 Request").Unprotect ("Secret")
For Each rCell In rInt
Set tCell = rCell.Offset(0, 0 - 1)
tCell.Locked = True
Next
For Each rCell In rInt
Set tCell = rCell.Offset(0, 0 - 2)
tCell.Locked = True
Next
For Each rCell In rInt
Set tCell = rCell.Offset(0, 0 - 3)
tCell.Locked = True
Next
For Each rCell In rInt
Set tCell = rCell.Offset(0, 0 - 4)
tCell.Locked = True
Next
For Each rCell In rInt
Set tCell = rCell.Offset(0, 0 - 5)
tCell.Locked = True
Next
For Each rCell In rInt
Set tCell = rCell.Offset(0, 0 - 6)
tCell.Locked = True
Next
For Each rCell In rInt
Set tCell = rCell.Offset(0, 0 - 7)
tCell.Locked = True
Next
For Each rCell In rInt
Set tCell = rCell.Offset(0, 0 - 8)
tCell.Locked = True
Next
For Each rCell In rInt
Set tCell = rCell.Offset(0, 0 - 9)
tCell.Locked = True
Next
For Each rCell In rInt
Set tCell = rCell.Offset(0, 0 - 10)
tCell.Locked = True
Next
For Each rCell In rInt
Set tCell = rCell.Offset(0, 0 - 11)
tCell.Locked = True
Next
For Each rCell In rInt
Set tCell = rCell.Offset(0, 0 - 12)
tCell.Locked = True
Next
For Each rCell In rInt
Set tCell = rCell.Offset(0, 0 - 13)
tCell.Locked = True
Next
Worksheets("CL 2 Request").Protect Password:="Secret", DrawingObjects:=False, contents:=True, Scenarios:=False, AllowFormattingCells:=True, AllowSorting:=True, AllowFiltering:=True
ElseIf rInt = Sheets("Data").Range("M3") Then
Worksheets("CL 2 Request").Unprotect ("Secret")
For Each rCell In rInt
Set tCell = rCell.Offset(0, 0 - 1)
tCell.Locked = True
Next
For Each rCell In rInt
Set tCell = rCell.Offset(0, 0 - 2)
tCell.Locked = False
Next
For Each rCell In rInt
Set tCell = rCell.Offset(0, 0 - 3)
tCell.Locked = False
Next
For Each rCell In rInt
Set tCell = rCell.Offset(0, 0 - 4)
tCell.Locked = False
Next
For Each rCell In rInt
Set tCell = rCell.Offset(0, 0 - 5)
tCell.Locked = True
Next
For Each rCell In rInt
Set tCell = rCell.Offset(0, 0 - 6)
tCell.Locked = False
Next
For Each rCell In rInt
Set tCell = rCell.Offset(0, 0 - 7)
tCell.Locked = False
Next
For Each rCell In rInt
Set tCell = rCell.Offset(0, 0 - 8)
tCell.Locked = False
Next
For Each rCell In rInt
Set tCell = rCell.Offset(0, 0 - 9)
tCell.Locked = False
Next
For Each rCell In rInt
Set tCell = rCell.Offset(0, 0 - 10)
tCell.Locked = False
Next
For Each rCell In rInt
Set tCell = rCell.Offset(0, 0 - 11)
tCell.Locked = False
Next
For Each rCell In rInt
Set tCell = rCell.Offset(0, 0 - 12)
tCell.Locked = False
Next
For Each rCell In rInt
Set tCell = rCell.Offset(0, 0 - 13)
tCell.Locked = False
Next
Worksheets("CL 2 Request").Protect Password:="Secret", DrawingObjects:=False, contents:=True, Scenarios:=False, AllowFormattingCells:=True, AllowSorting:=True, AllowFiltering:=True
End If
End Sub