Showing/Hiding Columns on Sheet 2 based on data validation in Sheet 1

[furt0414]

Hi All,

I'm new to VBA and could use some help. I have a data validation with Yes and No as the choices on sheet1 and based on the response would like to hide columns AJ:AW on sheet2. I have been digging around the forums for the last hour and haven't come across the answer yet. Can someone help me?

Here's what I have so far in my Sheet1 code:

Private Sub Worksheet_Change(ByVal Target As Range)
With Sheets("Sheet2")
Select Case Target.Address(False, False)
Case "A6"
.Columns("AJ:AW").Columns.Hidden = Target.Value = ""
End Select
End With


End Sub

 

[furt0414]

I should also mention that the data validation is in cell C6 on Sheet1, and in cell A6 I have an IF statement that returns "" if C6 = No.

 

[furt0414]

Solved thanks to some creative sleuthing. Here's the solution:

Private Sub Worksheet_Calculate()
Sheet2.Range("AJ:AW").EntireColumn.Hidden = Me.Range("A6") = ""
End Sub