Andy C 1987
New Member
- Joined
- Oct 24, 2023
- Messages
- 1
- Office Version
- 365
- Platform
- Windows
I have the below code which works exactly how i want it to on this particular worksheet however i want the cell i another worksheet to be the reference cell which changes depending on some other formulas runnning?
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Activate
If Not Application.Intersect(Range("A4"), Range(Target.Address)) Is Nothing Then
Select Case Target.Value
Case Is = "0": Rows("5:18").EntireRow.Hidden = True
Rows("19:34").EntireRow.Hidden = True
Rows("35:48").EntireRow.Hidden = True
Rows("49:64").EntireRow.Hidden = True
Rows("65:78").EntireRow.Hidden = True
Rows("79:94").EntireRow.Hidden = True
Rows("95:108").EntireRow.Hidden = True
Rows("109:124").EntireRow.Hidden = True
Rows("125:138").EntireRow.Hidden = True
Rows("139:154").EntireRow.Hidden = True
Rows("155:168").EntireRow.Hidden = True
Rows("169:184").EntireRow.Hidden = True
Rows("185:198").EntireRow.Hidden = True
Rows("199:214").EntireRow.Hidden = True
Rows("215:228").EntireRow.Hidden = True
Rows("229:244").EntireRow.Hidden = True
Rows("245:258").EntireRow.Hidden = True
Rows("259:274").EntireRow.Hidden = True
Rows("275:288").EntireRow.Hidden = True
Rows("289:302").EntireRow.Hidden = True
Case Is = "1": Rows("5:18").EntireRow.Hidden = False
Rows("19:34").EntireRow.Hidden = True
Rows("35:48").EntireRow.Hidden = True
Rows("49:64").EntireRow.Hidden = True
Rows("65:78").EntireRow.Hidden = True
Rows("79:94").EntireRow.Hidden = True
Rows("95:108").EntireRow.Hidden = True
Rows("109:124").EntireRow.Hidden = True
Rows("125:138").EntireRow.Hidden = True
Rows("139:154").EntireRow.Hidden = True
Rows("155:168").EntireRow.Hidden = True
Rows("169:184").EntireRow.Hidden = True
Rows("185:198").EntireRow.Hidden = True
Rows("199:214").EntireRow.Hidden = True
Rows("215:228").EntireRow.Hidden = True
Rows("229:244").EntireRow.Hidden = True
Rows("245:258").EntireRow.Hidden = True
Rows("259:274").EntireRow.Hidden = True
Rows("275:288").EntireRow.Hidden = True
Rows("289:302").EntireRow.Hidden = True
Case Is = "2": Rows("5:18").EntireRow.Hidden = False
Rows("19:34").EntireRow.Hidden = False
Rows("35:48").EntireRow.Hidden = True
Rows("49:64").EntireRow.Hidden = True
Rows("65:78").EntireRow.Hidden = True
Rows("79:94").EntireRow.Hidden = True
Rows("95:108").EntireRow.Hidden = True
Rows("109:124").EntireRow.Hidden = True
Rows("125:138").EntireRow.Hidden = True
Rows("139:154").EntireRow.Hidden = True
Rows("155:168").EntireRow.Hidden = True
Rows("169:184").EntireRow.Hidden = True
Rows("185:198").EntireRow.Hidden = True
Rows("199:214").EntireRow.Hidden = True
Rows("215:228").EntireRow.Hidden = True
Rows("229:244").EntireRow.Hidden = True
Rows("245:258").EntireRow.Hidden = True
Rows("259:274").EntireRow.Hidden = True
Rows("275:288").EntireRow.Hidden = True
Rows("289:302").EntireRow.Hidden = True
Case Is = "3": Rows("5:18").EntireRow.Hidden = False
Rows("19:34").EntireRow.Hidden = False
Rows("35:48").EntireRow.Hidden = False
Rows("49:64").EntireRow.Hidden = True
Rows("65:78").EntireRow.Hidden = True
Rows("79:94").EntireRow.Hidden = True
Rows("95:108").EntireRow.Hidden = True
Rows("109:124").EntireRow.Hidden = True
Rows("125:138").EntireRow.Hidden = True
Rows("139:154").EntireRow.Hidden = True
Rows("155:168").EntireRow.Hidden = True
Rows("169:184").EntireRow.Hidden = True
Rows("185:198").EntireRow.Hidden = True
Rows("199:214").EntireRow.Hidden = True
Rows("215:228").EntireRow.Hidden = True
Rows("229:244").EntireRow.Hidden = True
Rows("245:258").EntireRow.Hidden = True
Rows("259:274").EntireRow.Hidden = True
Rows("275:288").EntireRow.Hidden = True
Rows("289:302").EntireRow.Hidden = True
Case Is = "4": Rows("5:18").EntireRow.Hidden = False
Rows("19:34").EntireRow.Hidden = False
Rows("35:48").EntireRow.Hidden = False
Rows("49:64").EntireRow.Hidden = False
Rows("65:78").EntireRow.Hidden = True
Rows("79:94").EntireRow.Hidden = True
Rows("95:108").EntireRow.Hidden = True
Rows("109:124").EntireRow.Hidden = True
Rows("125:138").EntireRow.Hidden = True
Rows("139:154").EntireRow.Hidden = True
Rows("155:168").EntireRow.Hidden = True
Rows("169:184").EntireRow.Hidden = True
Rows("185:198").EntireRow.Hidden = True
Rows("199:214").EntireRow.Hidden = True
Rows("215:228").EntireRow.Hidden = True
Rows("229:244").EntireRow.Hidden = True
Rows("245:258").EntireRow.Hidden = True
Rows("259:274").EntireRow.Hidden = True
Rows("275:288").EntireRow.Hidden = True
Rows("289:302").EntireRow.Hidden = True
Case Is = "5": Rows("5:18").EntireRow.Hidden = False
Rows("19:34").EntireRow.Hidden = False
Rows("35:48").EntireRow.Hidden = False
Rows("49:64").EntireRow.Hidden = False
Rows("65:78").EntireRow.Hidden = False
Rows("79:94").EntireRow.Hidden = True
Rows("95:108").EntireRow.Hidden = True
Rows("109:124").EntireRow.Hidden = True
Rows("125:138").EntireRow.Hidden = True
Rows("139:154").EntireRow.Hidden = True
Rows("155:168").EntireRow.Hidden = True
Rows("169:184").EntireRow.Hidden = True
Rows("185:198").EntireRow.Hidden = True
Rows("199:214").EntireRow.Hidden = True
Rows("215:228").EntireRow.Hidden = True
Rows("229:244").EntireRow.Hidden = True
Rows("245:258").EntireRow.Hidden = True
Rows("259:274").EntireRow.Hidden = True
Rows("275:288").EntireRow.Hidden = True
Rows("289:302").EntireRow.Hidden = True
Case Is = "6": Rows("5:18").EntireRow.Hidden = False
Rows("19:34").EntireRow.Hidden = False
Rows("35:48").EntireRow.Hidden = False
Rows("49:64").EntireRow.Hidden = False
Rows("65:78").EntireRow.Hidden = False
Rows("79:94").EntireRow.Hidden = False
Rows("95:108").EntireRow.Hidden = True
Rows("109:124").EntireRow.Hidden = True
Rows("125:138").EntireRow.Hidden = True
Rows("139:154").EntireRow.Hidden = True
Rows("155:168").EntireRow.Hidden = True
Rows("169:184").EntireRow.Hidden = True
Rows("185:198").EntireRow.Hidden = True
Rows("199:214").EntireRow.Hidden = True
Rows("215:228").EntireRow.Hidden = True
Rows("229:244").EntireRow.Hidden = True
Rows("245:258").EntireRow.Hidden = True
Rows("259:274").EntireRow.Hidden = True
Rows("275:288").EntireRow.Hidden = True
Rows("289:302").EntireRow.Hidden = True
Case Is = "7": Rows("5:18").EntireRow.Hidden = False
Rows("19:34").EntireRow.Hidden = False
Rows("35:48").EntireRow.Hidden = False
Rows("49:64").EntireRow.Hidden = False
Rows("65:78").EntireRow.Hidden = False
Rows("79:94").EntireRow.Hidden = False
Rows("95:108").EntireRow.Hidden = False
Rows("109:124").EntireRow.Hidden = True
Rows("125:138").EntireRow.Hidden = True
Rows("139:154").EntireRow.Hidden = True
Rows("155:168").EntireRow.Hidden = True
Rows("169:184").EntireRow.Hidden = True
Rows("185:198").EntireRow.Hidden = True
Rows("199:214").EntireRow.Hidden = True
Rows("215:228").EntireRow.Hidden = True
Rows("229:244").EntireRow.Hidden = True
Rows("245:258").EntireRow.Hidden = True
Rows("259:274").EntireRow.Hidden = True
Rows("275:288").EntireRow.Hidden = True
Rows("289:302").EntireRow.Hidden = True
Case Is = "8": Rows("5:18").EntireRow.Hidden = False
Rows("19:34").EntireRow.Hidden = False
Rows("35:48").EntireRow.Hidden = False
Rows("49:64").EntireRow.Hidden = False
Rows("65:78").EntireRow.Hidden = False
Rows("79:94").EntireRow.Hidden = False
Rows("95:108").EntireRow.Hidden = False
Rows("109:124").EntireRow.Hidden = False
Rows("125:138").EntireRow.Hidden = True
Rows("139:154").EntireRow.Hidden = True
Rows("155:168").EntireRow.Hidden = True
Rows("169:184").EntireRow.Hidden = True
Rows("185:198").EntireRow.Hidden = True
Rows("199:214").EntireRow.Hidden = True
Rows("215:228").EntireRow.Hidden = True
Rows("229:244").EntireRow.Hidden = True
Rows("245:258").EntireRow.Hidden = True
Rows("259:274").EntireRow.Hidden = True
Rows("275:288").EntireRow.Hidden = True
Rows("289:302").EntireRow.Hidden = True
Case Is = "9": Rows("5:18").EntireRow.Hidden = False
Rows("19:34").EntireRow.Hidden = False
Rows("35:48").EntireRow.Hidden = False
Rows("49:64").EntireRow.Hidden = False
Rows("65:78").EntireRow.Hidden = False
Rows("79:94").EntireRow.Hidden = False
Rows("95:108").EntireRow.Hidden = False
Rows("109:124").EntireRow.Hidden = False
Rows("125:138").EntireRow.Hidden = False
Rows("139:154").EntireRow.Hidden = True
Rows("155:168").EntireRow.Hidden = True
Rows("169:184").EntireRow.Hidden = True
Rows("185:198").EntireRow.Hidden = True
Rows("199:214").EntireRow.Hidden = True
Rows("215:228").EntireRow.Hidden = True
Rows("229:244").EntireRow.Hidden = True
Rows("245:258").EntireRow.Hidden = True
Rows("259:274").EntireRow.Hidden = True
Rows("275:288").EntireRow.Hidden = True
Rows("289:302").EntireRow.Hidden = True
Case Is = "10": Rows("5:18").EntireRow.Hidden = False
Rows("19:34").EntireRow.Hidden = False
Rows("35:48").EntireRow.Hidden = False
Rows("49:64").EntireRow.Hidden = False
Rows("65:78").EntireRow.Hidden = False
Rows("79:94").EntireRow.Hidden = False
Rows("95:108").EntireRow.Hidden = False
Rows("109:124").EntireRow.Hidden = False
Rows("125:138").EntireRow.Hidden = False
Rows("139:154").EntireRow.Hidden = False
Rows("155:168").EntireRow.Hidden = True
Rows("169:184").EntireRow.Hidden = True
Rows("185:198").EntireRow.Hidden = True
Rows("199:214").EntireRow.Hidden = True
Rows("215:228").EntireRow.Hidden = True
Rows("229:244").EntireRow.Hidden = True
Rows("245:258").EntireRow.Hidden = True
Rows("259:274").EntireRow.Hidden = True
Rows("275:288").EntireRow.Hidden = True
Rows("289:302").EntireRow.Hidden = True
Case Is = "11": Rows("5:18").EntireRow.Hidden = False
Rows("19:34").EntireRow.Hidden = False
Rows("35:48").EntireRow.Hidden = False
Rows("49:64").EntireRow.Hidden = False
Rows("65:78").EntireRow.Hidden = False
Rows("79:94").EntireRow.Hidden = False
Rows("95:108").EntireRow.Hidden = False
Rows("109:124").EntireRow.Hidden = False
Rows("125:138").EntireRow.Hidden = False
Rows("139:154").EntireRow.Hidden = False
Rows("155:168").EntireRow.Hidden = False
Rows("169:184").EntireRow.Hidden = True
Rows("185:198").EntireRow.Hidden = True
Rows("199:214").EntireRow.Hidden = True
Rows("215:228").EntireRow.Hidden = True
Rows("229:244").EntireRow.Hidden = True
Rows("245:258").EntireRow.Hidden = True
Rows("259:274").EntireRow.Hidden = True
Rows("275:288").EntireRow.Hidden = True
Rows("289:302").EntireRow.Hidden = True
Case Is = "12": Rows("5:18").EntireRow.Hidden = False
Rows("19:34").EntireRow.Hidden = False
Rows("35:48").EntireRow.Hidden = False
Rows("49:64").EntireRow.Hidden = False
Rows("65:78").EntireRow.Hidden = False
Rows("79:94").EntireRow.Hidden = False
Rows("95:108").EntireRow.Hidden = False
Rows("109:124").EntireRow.Hidden = False
Rows("125:138").EntireRow.Hidden = False
Rows("139:154").EntireRow.Hidden = False
Rows("155:168").EntireRow.Hidden = False
Rows("169:184").EntireRow.Hidden = False
Rows("185:198").EntireRow.Hidden = True
Rows("199:214").EntireRow.Hidden = True
Rows("215:228").EntireRow.Hidden = True
Rows("229:244").EntireRow.Hidden = True
Rows("245:258").EntireRow.Hidden = True
Rows("259:274").EntireRow.Hidden = True
Rows("275:288").EntireRow.Hidden = True
Rows("289:302").EntireRow.Hidden = True
Case Is = "13": Rows("5:18").EntireRow.Hidden = False
Rows("19:34").EntireRow.Hidden = False
Rows("35:48").EntireRow.Hidden = False
Rows("49:64").EntireRow.Hidden = False
Rows("65:78").EntireRow.Hidden = False
Rows("79:94").EntireRow.Hidden = False
Rows("95:108").EntireRow.Hidden = False
Rows("109:124").EntireRow.Hidden = False
Rows("125:138").EntireRow.Hidden = False
Rows("139:154").EntireRow.Hidden = False
Rows("155:168").EntireRow.Hidden = False
Rows("169:184").EntireRow.Hidden = False
Rows("185:198").EntireRow.Hidden = False
Rows("199:214").EntireRow.Hidden = True
Rows("215:228").EntireRow.Hidden = True
Rows("229:244").EntireRow.Hidden = True
Rows("245:258").EntireRow.Hidden = True
Rows("259:274").EntireRow.Hidden = True
Rows("275:288").EntireRow.Hidden = True
Rows("289:302").EntireRow.Hidden = True
Case Is = "14": Rows("5:18").EntireRow.Hidden = False
Rows("19:34").EntireRow.Hidden = False
Rows("35:48").EntireRow.Hidden = False
Rows("49:64").EntireRow.Hidden = False
Rows("65:78").EntireRow.Hidden = False
Rows("79:94").EntireRow.Hidden = False
Rows("95:108").EntireRow.Hidden = False
Rows("109:124").EntireRow.Hidden = False
Rows("125:138").EntireRow.Hidden = False
Rows("139:154").EntireRow.Hidden = False
Rows("155:168").EntireRow.Hidden = False
Rows("169:184").EntireRow.Hidden = False
Rows("185:198").EntireRow.Hidden = False
Rows("199:214").EntireRow.Hidden = False
Rows("215:228").EntireRow.Hidden = True
Rows("229:244").EntireRow.Hidden = True
Rows("245:258").EntireRow.Hidden = True
Rows("259:274").EntireRow.Hidden = True
Rows("275:288").EntireRow.Hidden = True
Rows("289:302").EntireRow.Hidden = True
Case Is = "15": Rows("5:18").EntireRow.Hidden = False
Rows("19:34").EntireRow.Hidden = False
Rows("35:48").EntireRow.Hidden = False
Rows("49:64").EntireRow.Hidden = False
Rows("65:78").EntireRow.Hidden = False
Rows("79:94").EntireRow.Hidden = False
Rows("95:108").EntireRow.Hidden = False
Rows("109:124").EntireRow.Hidden = False
Rows("125:138").EntireRow.Hidden = False
Rows("139:154").EntireRow.Hidden = False
Rows("155:168").EntireRow.Hidden = False
Rows("169:184").EntireRow.Hidden = False
Rows("185:198").EntireRow.Hidden = False
Rows("199:214").EntireRow.Hidden = False
Rows("215:228").EntireRow.Hidden = False
Rows("229:244").EntireRow.Hidden = True
Rows("245:258").EntireRow.Hidden = True
Rows("259:274").EntireRow.Hidden = True
Rows("275:288").EntireRow.Hidden = True
Rows("289:302").EntireRow.Hidden = True
Case Is = "16": Rows("5:18").EntireRow.Hidden = False
Rows("19:34").EntireRow.Hidden = False
Rows("35:48").EntireRow.Hidden = False
Rows("49:64").EntireRow.Hidden = False
Rows("65:78").EntireRow.Hidden = False
Rows("79:94").EntireRow.Hidden = False
Rows("95:108").EntireRow.Hidden = False
Rows("109:124").EntireRow.Hidden = False
Rows("125:138").EntireRow.Hidden = False
Rows("139:154").EntireRow.Hidden = False
Rows("155:168").EntireRow.Hidden = False
Rows("169:184").EntireRow.Hidden = False
Rows("185:198").EntireRow.Hidden = False
Rows("199:214").EntireRow.Hidden = False
Rows("215:228").EntireRow.Hidden = False
Rows("229:244").EntireRow.Hidden = False
Rows("245:258").EntireRow.Hidden = True
Rows("259:274").EntireRow.Hidden = True
Rows("275:288").EntireRow.Hidden = True
Rows("289:302").EntireRow.Hidden = True
Case Is = "17": Rows("5:18").EntireRow.Hidden = False
Rows("19:34").EntireRow.Hidden = False
Rows("35:48").EntireRow.Hidden = False
Rows("49:64").EntireRow.Hidden = False
Rows("65:78").EntireRow.Hidden = False
Rows("79:94").EntireRow.Hidden = False
Rows("95:108").EntireRow.Hidden = False
Rows("109:124").EntireRow.Hidden = False
Rows("125:138").EntireRow.Hidden = False
Rows("139:154").EntireRow.Hidden = False
Rows("155:168").EntireRow.Hidden = False
Rows("169:184").EntireRow.Hidden = False
Rows("185:198").EntireRow.Hidden = False
Rows("199:214").EntireRow.Hidden = False
Rows("215:228").EntireRow.Hidden = False
Rows("229:244").EntireRow.Hidden = False
Rows("245:258").EntireRow.Hidden = False
Rows("259:274").EntireRow.Hidden = True
Rows("275:288").EntireRow.Hidden = True
Rows("289:302").EntireRow.Hidden = True
Case Is = "18": Rows("5:18").EntireRow.Hidden = False
Rows("19:34").EntireRow.Hidden = False
Rows("35:48").EntireRow.Hidden = False
Rows("49:64").EntireRow.Hidden = False
Rows("65:78").EntireRow.Hidden = False
Rows("79:94").EntireRow.Hidden = False
Rows("95:108").EntireRow.Hidden = False
Rows("109:124").EntireRow.Hidden = False
Rows("125:138").EntireRow.Hidden = False
Rows("139:154").EntireRow.Hidden = False
Rows("155:168").EntireRow.Hidden = False
Rows("169:184").EntireRow.Hidden = False
Rows("185:198").EntireRow.Hidden = False
Rows("199:214").EntireRow.Hidden = False
Rows("215:228").EntireRow.Hidden = False
Rows("229:244").EntireRow.Hidden = False
Rows("245:258").EntireRow.Hidden = False
Rows("259:274").EntireRow.Hidden = False
Rows("275:288").EntireRow.Hidden = True
Rows("289:302").EntireRow.Hidden = True
Case Is = "19": Rows("5:18").EntireRow.Hidden = False
Rows("19:34").EntireRow.Hidden = False
Rows("35:48").EntireRow.Hidden = False
Rows("49:64").EntireRow.Hidden = False
Rows("65:78").EntireRow.Hidden = False
Rows("79:94").EntireRow.Hidden = False
Rows("95:108").EntireRow.Hidden = False
Rows("109:124").EntireRow.Hidden = False
Rows("125:138").EntireRow.Hidden = False
Rows("139:154").EntireRow.Hidden = False
Rows("155:168").EntireRow.Hidden = False
Rows("169:184").EntireRow.Hidden = False
Rows("185:198").EntireRow.Hidden = False
Rows("199:214").EntireRow.Hidden = False
Rows("215:228").EntireRow.Hidden = False
Rows("229:244").EntireRow.Hidden = False
Rows("245:258").EntireRow.Hidden = False
Rows("259:274").EntireRow.Hidden = False
Rows("275:288").EntireRow.Hidden = False
Rows("289:302").EntireRow.Hidden = True
Case Is = "20": Rows("5:18").EntireRow.Hidden = False
Rows("19:34").EntireRow.Hidden = False
Rows("35:48").EntireRow.Hidden = False
Rows("49:64").EntireRow.Hidden = False
Rows("65:78").EntireRow.Hidden = False
Rows("79:94").EntireRow.Hidden = False
Rows("95:108").EntireRow.Hidden = False
Rows("109:124").EntireRow.Hidden = False
Rows("125:138").EntireRow.Hidden = False
Rows("139:154").EntireRow.Hidden = False
Rows("155:168").EntireRow.Hidden = False
Rows("169:184").EntireRow.Hidden = False
Rows("185:198").EntireRow.Hidden = False
Rows("199:214").EntireRow.Hidden = False
Rows("215:228").EntireRow.Hidden = False
Rows("229:244").EntireRow.Hidden = False
Rows("245:258").EntireRow.Hidden = False
Rows("259:274").EntireRow.Hidden = False
Rows("275:288").EntireRow.Hidden = False
Rows("289:302").EntireRow.Hidden = False
End Select
End If
End Sub
Last edited by a moderator: