Error run-time 13 with VBA - Excel

[FlavioMorrone]

Hi everybody,
I am trying to run the following coding in the macro:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 4 And Target.Row = 2 And Target.Value = "5" Then
        Application.Columns("L:P").Select
        Application.Selection.EntireColumn.Hidden = True
    Else
        Application.Columns("L:P").Select
        Application.Selection.EntireColumn.Hidden = False
    End If
End Sub

My goal is to hide columns from L to P based on the selection on the dropdown list in cell D2. However, by running the code, I bump into a series of problems:

• A window pops out saying "Error 13 run-time" everytime I insert or delete any row
• Many times, while working on the Excel sheet, I automatically happen to select the columns L:P even against my will, and this turns out to be quite annoying.

Would like to ask for help to solve these two issues, thank you so much in advance!

 

[steve the fish]

Hi. See if this works:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rng As Range

Set rng = Intersect(Target, Cells(2, 4))
If Not rng Is Nothing Then
   Columns("L:P").Hidden = (rng.Value = 5)
End If

End Sub

 

[FlavioMorrone]

Thanks man! it seems to work fine Have a great day!

 

[Dryver14]

Steve,

Is this line essentially a toggle?

Columns("L:P").Hidden = (rng.Value = 5)

 

[steve the fish]

Its going to produce a true or a false based on the value of the cell. This is more of a toggle in my mind:

Columns("L:P").Hidden = Not(Columns("L:P").Hidden)

 

[Dryver14]

It is in my mind too what I am asking is if range.value does not = 5 do the columns re-appear

 

[Dryver14]

Never mind, I see it does, love it.

 

[steve the fish]

Oh sorry yes they do because the test in the brackets is then false.