Hide / Unhide Columns

[APML]

Hi all, I'm not so great at VBA so hoping someone can help.
I'm after a simple macro.
When cell a1 = 1 Columns H:L are hidden,
When cell a1 = 2 Column H:L are unhidden

I only want this to apply to the worksheet named "Intra"
Cell a1 is on the worksheet named "Intra"

 

[rlv01]

Possibly something like this:

'this goes in the code module for worksheet Intra
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" Then
        Select Case Me.Range("A1").Value
        Case 1
            Me.Columns("H:L").Hidden = True
        Case 2
            Me.Columns("H:L").Hidden = False
        End Select
    End If
End Sub

 

[APML]

Possibly something like this:

'this goes in the code module for worksheet Intra
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" Then
        Select Case Me.Range("A1").Value
        Case 1
            Me.Columns("H:L").Hidden = True
        Case 2
            Me.Columns("H:L").Hidden = False
        End Select
    End If
End Sub

Possibly something like this:

'this goes in the code module for worksheet Intra
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" Then
        Select Case Me.Range("A1").Value
        Case 1
            Me.Columns("H:L").Hidden = True
        Case 2
            Me.Columns("H:L").Hidden = False
        End Select
    End If
End Sub

Thx it works well, but now I have another problem,
When I actually put 1 or 2 in cell A1 it does what it's meant to do
BUT
When 1 or 2 goes into A1 via a formula then it doesn't trigger :
i.e let's say in A1 I have the formula =E1, when I change E1 from 1 to 2 or 2 to 1, the value in A1 changes but the macro doesn't trigger, is there a way around this?

 

[rlv01]

A changing formula result in cell A1 won't trigger a change event so you need possible specify another cell, or think of another event to tie it to. For example, you could tie it to the worksheet activate event so it checks the status when the worksheet is activated. For example:

Private Sub Worksheet_Activate()
    Select Case Me.Range("A1").Value
    Case 1
        Me.Columns("H:L").Hidden = True
    Case 2
        Me.Columns("H:L").Hidden = False
    End Select
End Sub

 

[APML]

Hi, thx for your help, I ended up using the following, which solved the issue

Private Sub Worksheet_Calculate()

Dim ws As Worksheet: Set ws = ThisWorkbook.ActiveSheet
Dim kcell As Range: Set kcell = ws.Range("A1")

If kcell.Value = "1" Then
ws.Range("H:L").EntireColumn.Hidden = True
ElseIf kcell.Value = "2" Then
ws.Range("H:L").EntireColumn.Hidden = False
End If

End Sub