Třeba nějak tak?
Bohužel ani to mi nějak nejde.

Private Sub Worksheet_Change(ByVal Target As Range)
Set aCell = Range("A1")
Set aCell = Range("A2")
Set aCell = Range("A3")
Set aCell = Range("A4")
Set aCell = Range("A5")

If Not Intersect(Target, aCell) Is Nothing Then
Range("B28:AR28").EntireRow.Hidden = aCell = "1"
Range("B30:AR30").EntireRow.Hidden = aCell = "2"
Range("B32:AR32").EntireRow.Hidden = aCell = "3"
Range("B34:AR34").EntireRow.Hidden = aCell = "4"
Range("B36:AR36").EntireRow.Hidden = aCell = "5"
End If
End Sub