I have a VBA function set up whereby certain cells are displayed after ALL dropdowns are selected from 3 cells (D5,D6(Misc 1 or Misc 2) and D7)
I am looking to have 3 separate cases that return certain outcomes. For example, If D7 shows "CASE 1" this will only shows rows 15 & 19 (Row 17 will be hidden). If D7 shows "CASE 2" this will show rows 15 & 17 (19 will be hidden). Finally, if D7 shows "CASE 3" then this will show row 19 only (15 & 17 will be hidden).
PS - D6 only has 2 options "MISC 1" and "MISC 2". If "MISC 1" is selected, row 26 will show. If "MISC 2" is selected row 26 will be hidden.
I am also looking to add more options in i.e. "CASE 4,5,6,etc" that show and hide other rows but I'll tackle that another day.
I have this VBA but it doesn't work for CASE 1. Could someone offer their advice? PPS - I am new to VBA and it pickles my head at times.
Thanks.
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
If Range("D5").Value <> "" And Range("D6").Value <> "" And Range("D7").Value <> "" Then
If Target.Address = "$D$5" Or Target.Address = "$D$6" Or Target.Address = "$D$7" Then
If Range("D6").Value = "Misc" Then
Rows("25:25").EntireRow.Hidden = False
Else
Rows("25:25").EntireRow.Hidden = True
End If
Select Case Range("D5").Value
Case "ABC", "DEF"
If Range("D7").Value = "CASE1" Then
Rows("19:19").EntireRow.Hidden = False
Else
Rows("19:19").EntireRow.Hidden = True
End If
If Range("D7").Value = "CASE2" Then
Rows("15:15").EntireRow.Hidden = True
Else
Rows("15:15").EntireRow.Hidden = False
End If
Rows("17:17").EntireRow.Hidden = False
If Range("D7").Value = "CASE 2" Then
Rows("17:17").EntireRow.Hidden = True
Else
Rows("17:17").EntireRow.Hidden = False
End If
Rows("17:17").EntireRow.Hidden = False
End Select
End If
End If
Application.ScreenUpdating = True
End Sub
Bookmarks