How Hide columns according to a cell value

Question

I'm looking for hidding columns according to a cell value. For exemple when the value is 1 the I to BV columns have to be hide. When value is 2 O to BV columns have to be hidding but the I to O columns have to be visible.

My code works only for 1 and I don't find how can I do... Thank you for your help

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("B2")) Is Nothing Then
        If Target = 1 Then
            Columns("I:BV").EntireColumn.Hidden = True
        Else: Columns("I:BV").EntireColumn.Hidden = False
        End If
    End If
End Sub

Private Sub Worksheet_Change2(ByVal Target As Range)
    If Not Intersect(Target, Range("B2")) Is Nothing Then
        If Target = 2 Then
            Columns("O:BV").EntireColumn.Hidden = True
        Else: Columns("O:BV").EntireColumn.Hidden = False
        End If
    End If
End Sub

Private Sub Worksheet_Change3(ByVal Target As Range)
    If Not Intersect(Target, Range("B2")) Is Nothing Then
        If Target = 4 Then
            Columns("U:BV").EntireColumn.Hidden = True
        Else: Columns("U:BV").EntireColumn.Hidden = False
        End If
    End If
End Sub

Private Sub Worksheet_Change4(ByVal Target As Range)
    If Not Intersect(Target, Range("B2")) Is Nothing Then
        If Target = 5 Then
            Columns("AA:BV").EntireColumn.Hidden = True
        Else: Columns("AA:BV").EntireColumn.Hidden = False
        End If
    End If
End Sub

Private Sub Worksheet_Change5(ByVal Target As Range)
    If Not Intersect(Target, Range("B2")) Is Nothing Then
        If Target = 6 Then
            Columns("AG:BV").EntireColumn.Hidden = True
        Else: Columns("AG:BV").EntireColumn.Hidden = False
        End If
    End If
End Sub

Private Sub Worksheet_Change6(ByVal Target As Range)
    If Not Intersect(Target, Range("B2")) Is Nothing Then
        If Target = 7 Then
            Columns("AM:BV").EntireColumn.Hidden = True
        Else: Columns("AM:BV").EntireColumn.Hidden = False
        End If
    End If
End Sub

Private Sub Worksheet_Change7(ByVal Target As Range)
    If Not Intersect(Target, Range("B2")) Is Nothing Then
        If Target = 8 Then
            Columns("AS:BV").EntireColumn.Hidden = True
        Else: Columns("AS:BV").EntireColumn.Hidden = False
        End If
    End If
End Sub

Private Sub Worksheet_Change8(ByVal Target As Range)
    If Not Intersect(Target, Range("B2")) Is Nothing Then
        If Target = 9 Then
            Columns("AY:BV").EntireColumn.Hidden = True
        Else: Columns("AY:BV").EntireColumn.Hidden = False
        End If
    End If
End Sub

Private Sub Worksheet_Change9(ByVal Target As Range)
    If Not Intersect(Target, Range("B2")) Is Nothing Then
        If Target = 10 Then
            Columns("BE:BV").EntireColumn.Hidden = True
        Else: Columns("BE:BV").EntireColumn.Hidden = False
        End If
    End If
End Sub

Private Sub Worksheet_Change10(ByVal Target As Range)
    If Not Intersect(Target, Range("B2")) Is Nothing Then
        If Target = 11 Then
            Columns("BK:BV").EntireColumn.Hidden = True
        Else: Columns("BK:BV").EntireColumn.Hidden = False
        End If
    End If
End Sub

Private Sub Worksheet_Change11(ByVal Target As Range)
    If Not Intersect(Target, Range("B2")) Is Nothing Then
        If Target = 12 Then
            Columns("BQ:BV").EntireColumn.Hidden = True
        Else: Columns("BQ:BV").EntireColumn.Hidden = False
        End If
    End If
End Sub

Answer 1

You can do this in fewer lines:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim primaryCols As Range
If Not Intersect(Target, Range("B2")) Is Nothing Then
        Range(Columns(3 + Range("B2").Value * 6), Columns(74)).EntireColumn.Hidden = False
        Range(Columns(9), Columns(2 + Range("B2").Value * 6)).EntireColumn.Hidden = True
End If
End Sub

Basically it uses a little arithmetic to get your start column for those you want visible, and end column for those to hide, from column I onward.

Answer 2

Just calculate whether the column number is greater than the last column which you want to be visible:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("B2")) Is Nothing Then
        Dim i As Long
        Dim lastVisible As Long
        'Use cell B2 in the calculation, just in case Target is
        ' something like A1:D17
        lastVisible = 2 + Range("B2").Value * 6
        'That formula is calculating lastVisible such that:
        'If B2 is  1, lastVisible will be  8 (i.e. column H)
        'If B2 is  2, lastVisible will be 14 (i.e. column N)
        'If B2 is  3, lastVisible will be 20 (i.e. column T)
        'If B2 is  4, lastVisible will be 26 (i.e. column Z)
        '... etc, up to
        'If B2 is 11, lastVisible will be 68 (i.e. column BP)
        'If B2 is 12, lastVisible will be 74 (i.e. column BV)

        For i = 3 To 74
            Columns(i).Hidden = i > lastVisible
        Next
    End If
End Sub

Answer 3

One change event with multiple conditions tests within an IF statement, using ElseIf. Without writing it all for you, the following it the structure and key elements. There are plenty of examples on stack overflow to help.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("B2")) Is Nothing Then

        'Code to unhide all columns goes here.

        'Then test the contents of B2
        If Target = 1 Then
            Columns("I:BV").EntireColumn.Hidden = True
        ElseIf Target = 2 Then 
            Columns("O:BV").EntireColumn.Hidden = True
        ElseIf Target = 3 Then ......'Continue with rest of conditions
        End If
   End If

End Sub