Hide columns based on cell value

[cafeannalisa]

I have much the same question as but can't get the answers to work
Columns J1-N1 have headers 1 2 3 4 5
I want to hide columns based on S1, which takes values 1-5
If S1 = 1 Hide columns headed 2-5 i.e. K:N
If S1 = 2 Hide columns headed 3-5 i.e.L:N
If S1 = 3 Hide columns headed 4-5 i.e.M:N
If S1 = 4 Hide column headed 5 i.e. N
[If S1 = 5 No hiding]
I have tried a version of alansidman's code on the earlier thread without success.

Private Sub Worksheet_Change(ByVal Target As Range)

Set Target = Range(“S1")
If Target = 1 Then
columns(“K:N”).Hidden = True
ElseIf Target = 2 Then
columns("K:N").Hidden = False
columns(“L:N").Hidden = True
ElseIf Target = 3 Then
columns("K:N").Hidden = False
columns(“M:N").Hidden = True
ElseIf Target = 4 Then
columns("K:N").Hidden = False
columns(“N”).Hidden = True
End If
End Sub

 

[Fluff]

In what way doesn't it work?

 

[cafeannalisa]

In what way doesn't it work?

Nothing happens on sheet - maybe module setup not code?

 

[Fluff]

Ok, how is S1 being changed?

 

[cafeannalisa]

Ok, how is S1 being changed?

By selection from a drop-down menu

 

[Fluff]

Ok, in that case is the code in the relevant sheet module, or in a standard module?

 

[cafeannalisa]

Ok, in that case is the code in the relevant sheet module, or in a standard module?

i think it is a standard module - first time i have done this. Its in a 'Modules' folder at top level in the Project Sidebar

 

[Fluff]

Ok, you need to put the code in the relevant sheet module, in Xl right click the tab of the sheet you want the code to work on & select view code, then paste the code into the window that opens up.

 

[Fluff]

You could also use this code, which does the same thing

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address(0, 0) = "S1" Then
      Range("K:N").EntireColumn.Hidden = False
      Select Case Target
         Case 1 To 4
            Cells(1, Target + 10).Resize(, 5 - Target.Value).EntireColumn.Hidden = True
      End Select
   End If
End Sub

 

[cafeannalisa]

Ok, you need to put the code in the relevant sheet module, in Xl right click the tab of the sheet you want the code to work on & select view code, then paste the code into the window that opens up.

Yes thanks so much for a New Year breakthrough, I learned something crucial obviously!