Hiding Columns Based on a Changing Cell Range

[ERed1]

Hi everyone,
I am trying to write a code that hides columns based off of a cell range. So if "Dog" is anywhere in B18 to B25, then columns N:X will disappear.

Here is my code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("B18:B25").Value = "Dog" Then
Columns("N:X").EntireColumn.Hidden = False
Else
Columns("N:X").EntireColumn.Hidden = True
End If
End Sub

Even if I use just cell B18, columns N:X still disappear when Dog is entered, when it should not be disappearing.

I'm sure this is pretty simple, but I have been stuck on it for about a day and need some help. Thanks for the help!

 

[Fluff]

Hi & welcome to MrExcel
Do you want to hide the columns if the range contains Dog, or unhide them?

 

[ERed1]

I want the columns to unhide if the Range contains Dog. Hide if it doesn't.

 

[Fluff]

In that case how about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Not Intersect(Target, Range("B18:B25")) Is Nothing Then
      If Application.CountIf(Range("B18:B25"), "Dog") > 0 Then
         Columns("N:X").EntireColumn.Hidden = False
      Else
         Columns("N:X").EntireColumn.Hidden = True
      End If
   End If
End Sub

 

[ERed1]

In that case how about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Not Intersect(Target, Range("B18:B25")) Is Nothing Then
      If Application.CountIf(Range("B18:B25"), "Dog") > 0 Then
         Columns("N:X").EntireColumn.Hidden = False
      Else
         Columns("N:X").EntireColumn.Hidden = True
      End If
   End If
End Sub

That worked perfectly! Thank you!

 

[Fluff]

You're welcome & thanks for the feedback

 

[ERed1]

So I added Cat to the problem instead of Dog. Ranges are Z:AB. whenever cat is entered, the Dog columns appear too. Any solution for this?

 

[Fluff]

What code are you using?

 

[ERed1]

The one you made, except I switched the Dog to Cat and the columns from N:X to Z:AB

 

[ERed1]

I think I know why It is doing that. I have a button to bring information in from another sheet and the way I have it pasting it unhides the columns