Hide columns in one sheet based on a cell from another sheet - Excel VBA

Question

Inside an excel workbook I have sheet1 and sheet2.

In Sheet1 I have a cell that changes dynamically (cell A1). In Sheet2 I have a cell (B1) that copies the value from cell A1 from Sheet1. On Sheet 2 cells will hide when the value in B1 is 0.

Here is the VBA code from sheet2

Private Sub Worksheet_Change(ByVal Target As Range)

    If Range("B1").Value = "0" Then
        Columns("E:I").EntireColumn.Hidden = True
    Else
        Columns("E:I").EntireColumn.Hidden = False
    End If

End Sub

The thing is that when I change the value on Sheet1 nothing happens on Sheet2, and I don´t know why.

Answer 1

Use the below code in sheet 1 calculate event,

Private Sub Worksheet_Calculate()
   If Range("B1").Value = "0" Then
      Sheet2.Columns("E:I").EntireColumn.Hidden = True
   Else
      Sheet2.Columns("E:I").EntireColumn.Hidden = False
   End If
End Sub

and do some calculation in sheet1-B1 cell(say =0+0), if the result is 0 then it will hide the columns E:I in Sheet2.

Answer 2

Used code on sheet1 to hide columns on sheet2

Private Sub Worksheet_Change(ByVal Target As Range)

    If Range("A1").Value = "0" Then
        Sheet2.Columns("E:I").EntireColumn.Hidden = True
    Else
        Sheet2.Columns("E:I").EntireColumn.Hidden = False
    End If

End Sub

Worked great. Thanks for the tip vanathaiyan