Need Macro to Hide Columns Based on a DropDown Menu Selection

[crasherar82]

I am struggling with a MACRO. I need to hide a range of columns based on the selection of a dropdown menu. Menu selections are 1-5 with the range of hidden columns changing based on the selection. I am pretty new at using macros and I am having a hard time. The macro runs but it hides columns starting from the target field. Here is the marco I input

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("B5") = 1 Then
Application.Columns("E:L").Select
Application.Selection.EntireColumn.Hidden = True

ElseIf Range("B5") = 2 Then
Application.Columns("G:L").Select
Application.Selection.EntireColumn.Hidden = True

ElseIf Range("B5") = 3 Then
Application.Columns("I:L").Select
Application.Selection.EntireColumn.Hidden = True

ElseIf Range("B5") = 4 Then
Application.Columns("K:L").Select
Application.Selection.EntireColumn.Hidden = True
End If


End Sub

When exectued it hides columns B:L regardless of the selection in B5. Please help/advise.

 

[Fluff]

Hi & welcome to MrExcel.
How about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address <> "$B$5" Or Target.Value = "" Then Exit Sub
   Range("E:L").EntireColumn.Hidden = False
   Range(Choose(Target.Value, "E:L", "G:L", "I:L", "K:L")).EntireColumn.Hidden = True
End Sub

 

[crasherar82]

Hi & welcome to MrExcel.
How about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address <> "$B$5" Or Target.Value = "" Then Exit Sub
   Range("E:L").EntireColumn.Hidden = False
   Range(Choose(Target.Value, "E:L", "G:L", "I:L", "K:L")).EntireColumn.Hidden = True
End Sub

That worked for options 1-4! When 5 is selected, nothing should be hidden. When I select 5 I get an error
(Run-Time Error 1004 : Method 'Range' of object'_Worksheet' failed).

Now I just need to figure out why it worked. I have one other macro I need to run that is similar, maybe you can help me with that too? :D
I have a yes/no field (B7). If no is selected, I need to hide Columns D, F, H, J, and L.

This would be easier if I understood this more. Any recommended resources I can review to get better at this?
Thanks!

 

[Fluff]

This will resolve the problem if 5 is selected.

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address <> "$B$5" Or Target.Value = "" Then Exit Sub
   Range("E:L").EntireColumn.Hidden = False
   If Target.Value = 5 Then Exit Sub
   Range(Choose(Target.Value, "E:L", "G:L", "I:L", "K:L")).EntireColumn.Hidden = True
End Sub

Is the new request on the same sheet?

 

[crasherar82]

Yes, it is the same sheet. And I lied, I need to hide columns D, F, H, J, L and O

Also, the code you sent fixed the problem. THANK YOU!!!

 

[Fluff]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Value = "" Then Exit Sub
   If Not Intersect(Target, Range("B5")) Is Nothing Then
      Range("E:L").EntireColumn.Hidden = False
      If Target.Value = 5 Then Exit Sub
      Range(Choose(Target.Value, "E:L", "G:L", "I:L", "K:L")).EntireColumn.Hidden = True
   ElseIf Not Intersect(Target, Range("B7")) Is Nothing Then
      Range("D:D,F:F,H:H,J:J,L:L,O:O").EntireColumn.Hidden = LCase(Target.Value) = "no"
   End If
End Sub

 

[crasherar82]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Value = "" Then Exit Sub
   If Not Intersect(Target, Range("B5")) Is Nothing Then
      Range("E:L").EntireColumn.Hidden = False
      If Target.Value = 5 Then Exit Sub
      Range(Choose(Target.Value, "E:L", "G:L", "I:L", "K:L")).EntireColumn.Hidden = True
   ElseIf Not Intersect(Target, Range("B7")) Is Nothing Then
      Range("D:D,F:F,H:H,J:J,L:L,O:O").EntireColumn.Hidden = LCase(Target.Value) = "no"
   End If
End Sub

You are AMAZING!!! Thank you so much. You just saved me HOURS of work.

 

[Fluff]

Glad to help & thanks for the feedback