Conditionally Hide Columns based on cell result

Hello. I am new to manipulating excel via VBA. I am trying to conditionally hide an entire coloumn based on a cell result in the given column. I have gotten only the below code to work.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Range("B3").Value = 0 Then
        Columns("B").EntireColumn.Hidden = True
    Else
        Columns("B").EntireColumn.Hidden = False
    End If
End Sub

I can get this to hide coloumn B as I want, but it only works for coloumn B (obviously). I want it to work for a range of coloumns from B to BA such that B3=0-->hide colB , C3=""-->display col C , D3=0-->hide colD , ... etc. up to coloumn BA

I'm assuming the answer is as simple as nesting the above code in a while loop or something similar from B:BA, but I don't have the know-how to program such a macro.

Thanks in Advance!

This is one option

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Union(Range("$B$1:$BA$100"), Target).Address = Range("$B$1:$BA$100").Address Then
        If Target.Value = 0 Then Target.EntireColumn.Hidden = True
    End If
End Sub

Hi Dave;
Shouldn't this line

Originally Posted by davesexcel

    If Union(Range("$B$1:$BA$100"), Target).Address = Range("$B$1:$BA$100").Address Then

Be

   If Union(Range("$B$3:$BA$3"), Target).Address =  Range("$B$3:$BA$3").Address Then

I'm also curious. Mose people use Intersect() instead of Union(). Is there a reason you don't?

This code is run after any event in Excel correct?

Not all code is run after every event.
1) Code has to be in the sheet's module to run after an event. (Right Mouse click the sheet's tab and select "View Code")
2) The different macros run after different events.
Worksheet_Change runs after you change any cell's value (or a macro changes a cell's value).
Worksheet_SelectionChange runs after you change selection in the sheet.
Etc.

So it depends on when you want the macro to run. If you want it to constantly be checking to see if cells B3:BA3 have changed put it into Worksheet_SelectionChange, but if you only want to check if B3:BA3 has been changed then put into Worksheet_Change
I'm pretty sure that everyone would recommend you put it into Worksheet_Change

Originally Posted by montego

I'm still curious why the Range its referencing are rows 1:100, but, it seems to be working
Any one care to elaborate on why the B1:B100 Range Works?

You will discover that it hides the column when you change ANY cell in rows 1:100 to 0.
If you want it to hide the column only when row 3 is changed to 0 then use B3:BA3

I was messing around and noticed that alhtough the code worked the first time, when I scrolled over it hid every coloumn from B:BA, regardless of what was in row 3 of each coloumn. Deleted code in VBA editor, repasted it, scrolled through the sheet again and seems to be working.

Overall it looks like the code works, but gets screwed up when I manually unhide hidden columns by right clicking and then unhide. Deleting the Code and Repasting seems to fix it.

Is this not a solved thread?

Originally Posted by davesexcel

Is this not a solved thread?

The original question was answered and he marked it solved, but then realized that he really wanted more that he thought. He tried to improve on the answer himself, but got confused, so he asked me to help with the addition.

Originally Posted by montego

I can make a new thread If both of you think that is appropriate

I have no problem with leaving it the way it is. This has happened to me before, and this is the first time that a moderator has said anything about it.

My thoughts are that since the 2nd question is directly related to the original solution, I would leave it here. Any one who looks at the thread because they saw the title, could very easily be interested in the follow up, but I'll follow the moderator's advice.