How to hide/unhide specified column ranges across different sheet based on a single cell value

Question

Hello VBA Community,

I am new to VBA and struggling to find any existing guidance on how to create a macro to hide/unhide specific column ranges across different sheets based on a single cell value.

For context, I am working on a financial model and want to be able to hide certain columns, on three different sheets, based on a single value.

What I'm trying to accomplish:

If "Yes" dropdown is selected in the "Financial Input" tab (cell Y5), I want column ranges O:P to be visible on sheets "Financial Input" and "Financial Output", as well as column ranges I:J on sheet "Financial Statements". In the event of "No", I want all of these to be hidden instead. The below code I put together is not working. What am I doing wrong? Thank you in advance.

----------------------------------------------------------------------------

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim Rng As Range, Cell As Range

Set FI = Worksheets("Financial Input")
Set Rng = FI.Range("O:P")

Set FO = Worksheets("Financial Output")
Set Rng = FO.Range("O:P")

Set FS = Worksheets("Financial Statements")
Set Rng = FS.Range("I:J")

For Each Cell In Rng

If Worksheets("FI").Range("Y5").Value = "Yes" Then

Worksheets("FI").Columns("O:P").Visible = True  
  
Worksheets("FO").Columns("O:P").Visible = True  
  
Worksheets("FI").Columns("I:J").Visible = True  
  
  

ElseIf Worksheets("FI").Range("Y5").Value = "No" Then

Worksheets("FI").Columns("O:P").Visible = False  
     
Worksheets("FO").Columns("O:P").Visible = False  
  
Worksheets("FS").Columns("I:J").Visible = False  

 

End If
End Sub

Answer 1

Try this, it's simple and it works for me.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Worksheets("Financial Input").Range("Y5").Value = "Yes" Then

Worksheets("Financial Input").Columns("O:P").Hidden = False

Worksheets("Financial Output").Columns("O:P").Hidden = False

Worksheets("Financial Statements").Columns("I:J").Hidden = False

ElseIf Worksheets("Financial Input").Range("Y5").Value = "No" Then

Worksheets("Financial Input").Columns("O:P").Hidden = True

Worksheets("Financial Output").Columns("O:P").Hidden = True

Worksheets("Financial Statements").Columns("I:J").Hidden = True

End If

End Sub