ComboBox to Hide Columns

[JoRyBar]

Hello Gurus,

I am currently having trouble making my ComboBox ActiveX hide and unhide columns based on range of text in a row. I did not have an issue with the formula when it used to be a dropdown, but when I tried to convert using the ComboBox, I am having issues.

What I would like this code to do is the following: When a text is selected from the combo box, hide all the columns that DO NOT contain the text in rows B2:Z2 and show only the columns with the text. If the combo box is empty, it should show all columns.


This is the revised code I am working on:

Private Sub ComboBox1_Change()

Dim cl As Range
Dim Rng As Range, Fnd As Range

Set Target.Value = ComboBox1.Value

If Target.Address(0, 0) = ComboBox1.Value Then
If Target.Value = "" Then
Range("B:Z").EntireColumn.Hidden = False
Else
For Each cl In Range("B2:Z2")
cl.EntireColumn.Hidden = cl.Value <> Target.Value
Next cl
End If
End If

End Sub

I keep getting an object error. I tried to link the combobox to another cell, but then the selected cell will not activate, so the code will not work. Please help!! Thank you so much.

Best,
Excel Novice

 

[Fluff]

How about

Private Sub ComboBox1_Change()
Dim Cl As Range

If ComboBox1.Value = "" Then
   Range("B:Z").EntireColumn.Hidden = False
Else
   For Each Cl In Range("B2:Z2")
      Cl.EntireColumn.Hidden = Cl.Value <> ComboBox1.Value
   Next Cl
End If
End Sub