Excel: Hide a number of columns depends of a cell vaue


Hello all , I have a problem with a code. I want to hide a number of columns, according to a value of a cell. If value is ='1' unhide "E:K" and hide "E:K". If value is ='2' unhide "E:K" and hide "F:K" If value is ='3' unhide "E:K" and hide "G:K" . . If value is ='7' unhide "E:K" and hide "K" I tried something but i have a problem. It works if i select value fom 1 to 8 but if i make a copy and paste i receive an error (the columns are hide and unhide correctly). Run-time error '13': Type mismatch. The code is below: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 3 And Target.Row = 3 And Target.Value = "1" Then Application.Columns("E:K").Select Application.Selection.EntireColumn.Hidden = False Application.Columns("E:L").Select Application.Selection.EntireColumn.Hidden = True ElseIf Target.Column = 3 And Target.Row = 3 And Target.Value = "2" Then Application.Columns("E:K").Select Application.Selection.EntireColumn.Hidden = False Application.Columns("F:K").Select Application.Selection.EntireColumn.Hidden = True ElseIf Target.Column = 3 And Target.Row = 3 And Target.Value = "3" Then Application.Columns("E:K").Select Application.Selection.EntireColumn.Hidden = False Application.Columns("G:K").Select Application.Selection.EntireColumn.Hidden = True ElseIf Target.Column = 3 And Target.Row = 3 And Target.Value = "4" Then Application.Columns("E:K").Select Application.Selection.EntireColumn.Hidden = False Application.Columns("H:K").Select Application.Selection.EntireColumn.Hidden = True ElseIf Target.Column = 3 And Target.Row = 3 And Target.Value = "5" Then Application.Columns("E:K").Select Application.Selection.EntireColumn.Hidden = False Application.Columns("I:K").Select Application.Selection.EntireColumn.Hidden = True ElseIf Target.Column = 3 And Target.Row = 3 And Target.Value = "6" Then Application.Columns("E:K").Select Application.Selection.EntireColumn.Hidden = False Application.Columns("J:K").Select Application.Selection.EntireColumn.Hidden = True ElseIf Target.Column = 3 And Target.Row = 3 And Target.Value = "7" Then Application.Columns("E:K").Select Application.Selection.EntireColumn.Hidden = False Application.Columns("K:K").Select Application.Selection.EntireColumn.Hidden = True ElseIf Target.Column = 3 And Target.Row = 3 And Target.Value = "8" Then Application.Columns("E:K").Select Application.Selection.EntireColumn.Hidden = False End If End Sub Have someone an idea about this error ? I`m noob in VBA but i like to try new things ! Thanks !


This question generated 9 answers. To proceed to the answers, click here.

This thread is current as of February 11, 2018.


For more resources for Microsoft Excel