[
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Sheet1.DTPicker1
DTPicker1.Value = Format(DTPicker1.Value, "dd/mm/yyyy")
.Height = 20
.Width = 20
If Not Intersect(Target, Range("G24:G25")) Is Nothing Then
.Visible = True
.Top = Target.Top
.Left = Target.Offset(0, 1).Left
.LinkedCell = Target.Address
Else
.Visible = False
End If
End With
Private Sub Worksheet_Change(ByVal Target As Range)
ThisWorkbook.Worksheets("Sheet1").Unprotect Password:="123"
'Unprotect
If Range("B20").Value = "Yes" Then
Rows("26:40").EntireRow.Hidden = False
End If
If Range("B20").Value = "No" Then
Rows("26:40").EntireRow.Hidden = True
ElseIf Range("B20").Value = 0 Then
Rows("26:40").EntireRow.Hidden = True
ThisWorkbook.Worksheets("Sheet1").Protect Password:="123"
'Protect
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
ThisWorkbook.Worksheets("Sheet1").Unprotect Password:="123"
'Unprotect
If Range("B22").Value <20 Then
Rows("23"). Hidden = False
Rows("41"). Hidden = False
End If
If Range("B22").Value >20 Then
Rows("23"). Hidden = True
Rows("41"). Hidden = True
End if
ThisWorkbook.Worksheets("Sheet1").Unprotect Password:="123"
'Unprotect
If Range("B27").Value <20 Then
Rows("28"). Hidden = False
Rows("41"). Hidden = False
End If
If Range("B27").Value >20 Then
Rows("28"). Hidden = True
Rows("41"). Hidden = True
ThisWorkbook.Worksheets("Sheet1").Protect Password:="123"
'Protect
End If
ThisWorkbook.Worksheets("Sheet1").Unprotect Password:="123"
'Unprotect
If Range("B32").Value <20 Then
Rows("33"). Hidden = False
Rows("41"). Hidden = False
End If
If Range("B32").Value >20 Then
Rows("33"). Hidden = True
Rows("41"). Hidden = True
ThisWorkbook.Worksheets("Sheet1").Protect Password:="123"
'Protect
End If
ThisWorkbook.Worksheets("Sheet1").Unprotect Password:="123"
'Unprotect
If Range("B37").Value <20 Then
Rows("38"). Hidden = False
Rows("41"). Hidden = False
End If
If Range("B37").Value >20 Then
Rows("38"). Hidden = True
Rows("41"). Hidden = True
ThisWorkbook.Worksheets("Sheet1").Protect Password:="123"
'Protect
End If
End Sub
]
- that sounds unreliable to me, but I guess it depends on which combination of values are possible in B22, B27, B32 or B37A1 - Row 41 should appear in any of above rule
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range, Hide As Boolean
Dim T As Range: Set T = Target
If T.CountLarge > 1 Then Exit Sub
If T < 20 Then Hide = False Else Hide = True
Select Case T.Address(0, 0)
Case "B20": Set rng = Rows("26:40"): If T = "Yes" Then Hide = False Else Hide = True
Case "B22": Set rng = Union(Rows(23), Rows(41))
Case "B27": Set rng = Union(Rows(28), Rows(41))
Case "B32": Set rng = Union(Rows(33), Rows(41))
Case "B37": Set rng = Union(Rows(38), Rows(41))
End Select
rng.EntireRow.Hidden = Hide
End Sub