Not sure if I'm doing something wrong, but I did the second part of the code to hide columns as a test. If I delete a cell from I4, it hides all columns in that sheet.
Private Sub Worksheet_Change(ByVal Target As Range)
Worksheet_Change_B Target
Worksheet_Change_V Target
End Sub
Private Sub Worksheet_Change_B(ByVal Target As Range)
Dim ws As Worksheet, blHide As Boolean, Rw As Long
Const sPW As String = "pass1234"
Application.ScreenUpdating = False
'==================== Beauty Expert ====================
If Not Intersect(Target, ActiveSheet.Range("E4:E33")) Is Nothing Then
blHide = (Target.Value = ""): Rw = Target.Row
For Each ws In Sheets(Array("P1", "P2", "P3", "P4", "P5", "P6", "P7", "P8", "P9", "P10", "P11", "P12", "P13"))
With ws
.Unprotect (sPW)
.Range(Rw & ":" & Rw & "," & Rw + 41 & ":" & Rw + 41 & "," & _
Rw + 82 & ":" & Rw + 82 & "," & Rw + 123 & ":" & Rw + 123).EntireRow.Hidden = blHide
.Protect (sPW)
.EnableSelection = xlUnlockedCells
End With
Next
With Sheets("Extra Week")
.Unprotect (sPW)
.Range(Rw & ":" & Rw).EntireRow.Hidden = blHide
.Protect (sPW)
.EnableSelection = xlUnlockedCells
End With
With Sheets("Cosmetician Sales")
.Unprotect (sPW)
.Range(Rw & ":" & Rw & "," & Rw + 31 & ":" & Rw + 31).EntireRow.Hidden = blHide
.Protect (sPW)
.EnableSelection = xlUnlockedCells
End With
With Sheets("CAST")
.Unprotect (sPW)
.Range((Rw - 4) * 19 + 1 & ":" & (Rw - 3) * 19).EntireRow.Hidden = blHide
.Protect (sPW)
.EnableSelection = xlUnlockedCells
End With
End If
Application.ScreenUpdating = True
End Sub
Private Sub Worksheet_Change_V(ByVal Target As Range)
Dim ws As Worksheet, blHide As Boolean, Cl As Long
Const sPW As String = "pass1234"
Application.ScreenUpdating = False
'==================== Beauty Expert ====================
If Not Intersect(Target, ActiveSheet.Range("I4:I33")) Is Nothing Then
blHide = (Target.Value = ""): Cl = Target.Column
For Each ws In Sheets(Array("P1", "P2", "P3", "P4", "P5", "P6", "P7", "P8", "P9", "P10", "P11", "P12", "P13"))
With ws
.Unprotect (sPW)
.Range(Cl & ":" & Cl).EntireColumn.Hidden = blHide
.Protect (sPW)
.EnableSelection = xlUnlockedCells
End With
Next
With Sheets("Extra Week")
.Unprotect (sPW)
.Range(Cl & ":" & Cl).EntireColumn.Hidden = blHide
.Protect (sPW)
.EnableSelection = xlUnlockedCells
End With
End If
Application.ScreenUpdating = True
End Sub
Bookmarks