miamiman33176
Board Regular
- Joined
- Jan 9, 2009
- Messages
- 97
I am trying to get this formula to work based upon a value on another cell on another sheet. The VBA code works very well when I select and place a number in the "BX3" cell, but when I use a reference to "ADMIN!J13" it will not work. Even if I tell cell "BX3" to value "ADMIN!J13" it will not work. If I tell "BX3" to get its value from "ADMIN!J13" it works once, when I click out of "BX3" and ir will not work again. Below is my code. Any suggestions?
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("BX3")) Is Nothing Then
If Target.CountLarge > 1 Then Exit Sub
Select Case Target.Address(0, 0)
Case "BX3"
Select Case Target.Value
Case Is = "0": Rows("8:60").EntireRow.Hidden = True
Case Is = "1": Rows("9:60").EntireRow.Hidden = True
Rows("7:8").EntireRow.Hidden = False
Case Is = "2": Rows("10:60").EntireRow.Hidden = True
Rows("8:9").EntireRow.Hidden = False
Case Is = "3": Rows("11:60").EntireRow.Hidden = True
Rows("8:10").EntireRow.Hidden = False
Case Is = "4": Rows("12:60").EntireRow.Hidden = True
Rows("8:11").EntireRow.Hidden = False
Case Is = "5": Rows("13:60").EntireRow.Hidden = True
Rows("8:12").EntireRow.Hidden = False
Case Is = "6": Rows("14:60").EntireRow.Hidden = True
Rows("8:13").EntireRow.Hidden = False
Case Is = "7": Rows("15:60").EntireRow.Hidden = True
Rows("8:14").EntireRow.Hidden = False
Case Is = "8": Rows("16:60").EntireRow.Hidden = True
Rows("8:15").EntireRow.Hidden = False
Case Is = "9": Rows("17:60").EntireRow.Hidden = True
Rows("8:16").EntireRow.Hidden = False
Case Is = "10": Rows("18:60").EntireRow.Hidden = True
Rows("8:17").EntireRow.Hidden = False
Case Is = "11": Rows("19:60").EntireRow.Hidden = True
Rows("8:18").EntireRow.Hidden = False
Case Is = "12": Rows("20:60").EntireRow.Hidden = True
Rows("8:19").EntireRow.Hidden = False
Case Is = "13": Rows("21:60").EntireRow.Hidden = True
Rows("8:20").EntireRow.Hidden = False
Case Is = "14": Rows("22:60").EntireRow.Hidden = True
Rows("8:21").EntireRow.Hidden = False
Case Is = "15": Rows("23:60").EntireRow.Hidden = True
Rows("8:22").EntireRow.Hidden = False
Case Is = "16": Rows("24:60").EntireRow.Hidden = True
Rows("8:23").EntireRow.Hidden = False
Case Is = "17": Rows("25:60").EntireRow.Hidden = True
Rows("8:24").EntireRow.Hidden = False
Case Is = "18": Rows("26:60").EntireRow.Hidden = True
Rows("8:25").EntireRow.Hidden = False
Case Is = "19": Rows("27:60").EntireRow.Hidden = True
Rows("8:26").EntireRow.Hidden = False
Case Is = "20": Rows("28:60").EntireRow.Hidden = True
Rows("8:27").EntireRow.Hidden = False
Case Is = "21": Rows("29:60").EntireRow.Hidden = True
Rows("8:28").EntireRow.Hidden = False
Case Is = "22": Rows("30:60").EntireRow.Hidden = True
Rows("8:29").EntireRow.Hidden = False
Case Is = "23": Rows("31:60").EntireRow.Hidden = True
Rows("8:30").EntireRow.Hidden = False
Case Is = "24": Rows("32:60").EntireRow.Hidden = True
Rows("8:31").EntireRow.Hidden = False
Case Is = "25": Rows("33:60").EntireRow.Hidden = True
Rows("8:32").EntireRow.Hidden = False
Case Is = "26": Rows("34:60").EntireRow.Hidden = True
Rows("8:33").EntireRow.Hidden = False
Case Is = "27": Rows("35:60").EntireRow.Hidden = True
Rows("8:34").EntireRow.Hidden = False
Case Is = "28": Rows("36:60").EntireRow.Hidden = True
Rows("8:35").EntireRow.Hidden = False
Case Is = "29": Rows("37:60").EntireRow.Hidden = True
Rows("8:36").EntireRow.Hidden = False
Case Is = "30": Rows("38:60").EntireRow.Hidden = True
Rows("8:37").EntireRow.Hidden = False
Case Is = "31": Rows("39:60").EntireRow.Hidden = True
Rows("8:38").EntireRow.Hidden = False
Case Is = "32": Rows("40:60").EntireRow.Hidden = True
Rows("8:39").EntireRow.Hidden = False
Case Is = "33": Rows("41:60").EntireRow.Hidden = True
Rows("8:40").EntireRow.Hidden = False
Case Is = "34": Rows("42:60").EntireRow.Hidden = True
Rows("8:41").EntireRow.Hidden = False
Case Is = "35": Rows("43:60").EntireRow.Hidden = True
Rows("8:42").EntireRow.Hidden = False
Case Is = "36": Rows("44:60").EntireRow.Hidden = True
Rows("8:43").EntireRow.Hidden = False
Case Is = "37": Rows("45:60").EntireRow.Hidden = True
Rows("8:44").EntireRow.Hidden = False
Case Is = "38": Rows("46:60").EntireRow.Hidden = True
Rows("8:45").EntireRow.Hidden = False
Case Is = "39": Rows("47:60").EntireRow.Hidden = True
Rows("8:46").EntireRow.Hidden = False
Case Is = "40": Rows("48:60").EntireRow.Hidden = True
Rows("8:47").EntireRow.Hidden = False
Case Is = "41": Rows("49:60").EntireRow.Hidden = True
Rows("8:48").EntireRow.Hidden = False
Case Is = "42": Rows("50:60").EntireRow.Hidden = True
Rows("8:49").EntireRow.Hidden = False
Case Is = "43": Rows("51:60").EntireRow.Hidden = True
Rows("8:50").EntireRow.Hidden = False
Case Is = "44": Rows("52:60").EntireRow.Hidden = True
Rows("8:51").EntireRow.Hidden = False
Case Is = "45": Rows("53:60").EntireRow.Hidden = True
Rows("8:52").EntireRow.Hidden = False
Case Is = "46": Rows("54:60").EntireRow.Hidden = True
Rows("8:53").EntireRow.Hidden = False
Case Is = "47": Rows("55:60").EntireRow.Hidden = True
Rows("8:54").EntireRow.Hidden = False
Case Is = "48": Rows("56:60").EntireRow.Hidden = True
Rows("8:55").EntireRow.Hidden = False
Case Is = "49": Rows("57:60").EntireRow.Hidden = True
Rows("8:56").EntireRow.Hidden = False
Case Is = "50": Rows("58:60").EntireRow.Hidden = True
Rows("8:57").EntireRow.Hidden = False
Case Is = "51": Rows("59:60").EntireRow.Hidden = True
Rows("8:58").EntireRow.Hidden = False
Case Is = "52": Rows("59:60").EntireRow.Hidden = True
Rows("8:59").EntireRow.Hidden = False
Case Is > 52: Rows("8:59").EntireRow.Hidden = True 'Canada range is hidde
Rows("60:61").EntireRow.Hidden = False
End Select
End Select
End If
End Sub
Last edited by a moderator: