VBA - Code to hide column(s) not working

[mark9988]

Hello -

I have an IF statement formula in cell C:515 that produces a value of 1, 2, 3, 4, or 5. My issue is that the code below does not hide/unhide the corresponding columns based on the formula in cell C:515.

However, when I manually type a value in cell C:515, the code works perfectly fine. Any thoughts on why this code is not working with the IF statement ?

Thanks!!



Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = ("$C$515") Then
If Target.Text = "1" Then
Columns("H:H").EntireColumn.Hidden = False
Columns("D:G").EntireColumn.Hidden = True

ElseIf Target.Text = "2" Then
Columns("G:H").EntireColumn.Hidden = False
Columns("D:F").EntireColumn.Hidden = True

ElseIf Target.Text = "3" Then
Columns("F:H").EntireColumn.Hidden = False
Columns("D:E").EntireColumn.Hidden = True

ElseIf Target.Text = "4" Then
Columns("E:H").EntireColumn.Hidden = False
Columns("D:D").EntireColumn.Hidden = True

ElseIf Target.Text = "5" Then
Columns("D:H").EntireColumn.Hidden = False

End If
End If
End Sub

 

[Akuini]

Try changing

Private Sub Worksheet_Change(ByVal Target As Range)

to:

Private Sub Worksheet_Calculate()

 

[Fluff]

How about

Private Sub Worksheet_Calculate()
   Range("D:H").EntireColumn.Hidden = True
   With Range("C515")
      If .Value > 0 Then
         Range("H:H").Offset(, -.Value + 1).Resize(, .Value).EntireColumn.Hidden = False
      End If
   End With
End Sub

But this will run whenever any cell on the sheet is recalculated.

 

[mark9988]

Thank you Akuini and Fluff for your support!

Hope you two have a great weekend!

 

[Fluff]

Glad we could help & thanks for the feedback.