Coding for multiple Hide Columns based on cell value on different worksheet

[verze]

Hi all,

Could you please assist me in adding multiple hide columns to this code that uses a cell value to control columns on another worksheet

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    Dim Value As Range, Cells As Range, Cell As Variant
    If Not Intersect(Target, Range("$B$8")).Value = 0 Then
        Sheets("Sheet1").Columns("D:E").EntireColumn.Hidden = False
    Else
        Sheets("Sheet1").Columns("D:E").EntireColumn.Hidden = True
    End If


End Sub

I have multiple cell values ranging from B8 to B20 (there are also gaps in between) and each cell value controls two columns.

I'd like it so that if any of them are zero/blank, that the connected two columns are hidden. The code above works for one but I dont know how to add more.. It was put into the sheets code

Would greatly appreciate some help

 

[LloydFinancials]

See if this helps.

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    Dim Value As Range, Cells As Range, Cell As Variant
    If Not Intersect(Target, Range("$B$8")).Value = 0 Then
        Sheets("Sheet1").Columns("D:E").EntireColumn.Hidden = False
    Else
        Sheets("Sheet1").Columns("D:E").EntireColumn.Hidden = True
    End If
    If Not Intersect(Target, Range("$B$9")).Value = 0 Then
        Sheets("Sheet1").Columns("F:G").EntireColumn.Hidden = False
    Else
        Sheets("Sheet1").Columns("F:G").EntireColumn.Hidden = True
    End If
End Sub

Luke

 

[verze]

Unfortunately that wont work either... Comes up with object variable or with block variable not set?

 

[LloydFinancials]

Appreciate the response. I am looking into it.

 

[LloydFinancials]

Here is code for Worksheet_SelectionChange that is working for me.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Range("B8").Value = 0 Then
        Columns("D:E").EntireColumn.Hidden = True
    Else
        Columns("D:E").EntireColumn.Hidden = False
    End If
    
    If Range("B9").Value = 0 Then
        Columns("I:J").EntireColumn.Hidden = True
    Else
        Columns("I:J").EntireColumn.Hidden = False
    End If
    
End Sub

 

[verze]

`can you make it so it controls the columns on another worksheet please?

 

[LloydFinancials]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Range("B8").Value = 0 Then
        Sheets("Sheet2").Columns("D:E").EntireColumn.Hidden = True
    Else
        Sheets("Sheet2").Columns("D:E").EntireColumn.Hidden = False
    End If
    
    If Range("B9").Value = 0 Then
        Sheets("Sheet2").Columns("I:J").EntireColumn.Hidden = True
    Else
        Sheets("Sheet2").Columns("I:J").EntireColumn.Hidden = False
    End If
    
End Sub

 

[verze]

That worked perfectly! thanks very much. I had read that worksheet_selectionchange wasnt good so didn't use it... glad you proved me wrong.

 

[LloydFinancials]

Thank you for the feedback. Glad I could help.