Hide Multiple Columns based on a cell value - "hide these or hide these or these"

[jwing3]

I am working tying to have the ability to hide columns based on a cell value where
Cell = L2
If the value in L2 = "Q1" then columns AD:BJ should be hidden, all other columns should be visible
If the value in L2 = "Q2" then columns T:AD & AO:BJ should be hidden, all other columns should be visible
If the value in L2 = "Q3" then columns T:AO & AZ:BJ should be hidden, all other columns should be visible
If the value in L2 = "Q4" then columns T:AZ should be hidden, all other columns should be visible

The following is what I have, but obviously I am missing something because it is not working.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("L2").Value = "Q1" Then
Columns("AD:BJ").EntireColumn.Hidden = True
ElseIf Range("L2").Value = "Q2" Then
Columns("T:AD,AO:BJ").EntireColumn.Hidden = True
ElseIf Range("L2").Value = "Q3" Then
Columns("T:AO,AZ:BJ").EntireColumn.Hidden = True
ElseIf Range("L2").Value = "Q4" Then
Columns("T:AZ").EntireColumn.Hidden = True
Else
Columns("T:BJ").EntireColumn.Hidden = False
End If
End Sub

Any Help is appreciated - J

 

[JLGWhiz]

Maybe if I explain what the code does it will clear things up.

1. If you make a change in cell M2, it will hide the sheets. It will not hide the columns because cell L2 was not the target.
2. If you make a change in cell L2, it will hide the columns according to the value entered in cell L2. It will also hide the sheets if the Brandon criteria is met.
3. The sheets criteria is not dependent on the target value, but the columns criteria is dependent on cell L2 being the target.

So if you change cell L2 and the value in that cell is Q1, Q2, Q3 or Q4 then the columns should hide accordingly. Otherwise, the columns will not hide.

 

[JLGWhiz]

Continued:
This is exactly what the two codes were doing before as separate procedures. The only need for combining them into one procedure was to satisfy the limitations of the Excel application which will not allow two of the same event procedures in one code module, In a regular code module you can just change the name of one procedure, but in the worksheet code module the title of the event procedure is similar to an Excel function. The event procedure title line must be exactly as the VBA application expects it to be or it will not execute as intended. So in writing the code, both the Excel limitations and the VBA limitations have to be adhered to or the code fails.

 

[jwing3]

JLG I have got it working now with only one issue
The input in cell L2 is equal of a formula. If I let the formula run, the columns do not hide.
however, if I manually change the content of L2 then the macro runs the way it should and the columns hide.

Would having the contents of L2 be based off of a formula make a difference?
if so what can I do to correct?

And thank you again for all your attention and help

 

[JLGWhiz]

If L2 is chaged by the result of a formula then you cannot combine the two procedures under a Worksheet_Change event because Excel does not see calculated results as changes. You would need to go back to separate procedures. as shown below.

Private Sub Worksheet_Change(ByVal Target As Range)    
    Dim ws As Worksheet
    Application.EnableEvents = False
    For Each ws In Worksheets
        If ws.Name <> "Brandon" And ws.Name <> Worksheets("Brandon").Range("M2").Value Then
            ws.Visible = False
        End If
        If ws.Name = Worksheets("Brandon").Range("M2").Value Then
            ws.Visible = True
        End If
    Next ws
    Application.EnableEvents = True
End Sub

Private Sub Worksheet_Calculate()
    ActiveSheet.Columns.Hidden = False
        If Range("L2").Value = "Q1" Then
            Range("AD:BJ").EntireColumn.Hidden = True
        ElseIf Range("L2").Value = "Q2" Then
            Range("T:AD,AO:BJ").EntireColumn.Hidden = True
        ElseIf Range("L2").Value = "Q3" Then
            Range("T:AO,AZ:BJ").EntireColumn.Hidden = True
        ElseIf Range("L2").Value = "Q4" Then
            Range("T:AZ").EntireColumn.Hidden = True
        Else
            Range("T:BJ").EntireColumn.Hidden = False
        End If
End Sub

 

[jwing3]

That is perfect - it all works!!
Thank you so much for your help

 

[JLGWhiz]

That is perfect - it all works!!
Thank you so much for your help

You're welcome,
Regards, JLG