Maybe ...
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
With Target
If .Column <> 3 Then Exit Sub
On Error GoTo Oops
Application.EnableEvents = False
If .Count = 1 Then
If IsEmpty(.Value) Then
.EntireRow.Range("D1").Value = "L"
.EntireRow.Range("E1:L1").ClearContents
End If
End If
End With
With Range("A4:A90")
.FormulaR1C1 = "=IF(RC[2]>0,SUM(MAX(R3C:R[-1]C),1),"""")"
.Value = .Value
End With
For Each cell In Worksheets("Semester1").Range("B10:B90").Cells
If IsEmpty(cell.Value) Then
cell.EntireRow.Range("C1:G1, K1:O1").Value = "L"
cell.EntireRow.Hidden = True
End If
Next cell
For Each cell In Worksheets("Semester2").Range("B10:B90").Cells
If IsEmpty(cell.Value) Then
cell.EntireRow.ClearContents
cell.EntireRow.Hidden = True
End If
Next cell
Done:
Application.EnableEvents = True
Exit Sub
Oops:
MsgBox "Oops"
Resume Done
End Sub
Bookmarks