Hide Columns Based on Other Sheet Cell Value

[VrRotate]

Hello,

I've used several examples and can't get this to work.

Condition:
Drop down on Sheet1 cell B7 Yes or No

If No hide Sheet2 Columns CN - EL
If Yes show Sheet2 Columns CN - EL

Want to work in real time as user selects Yes or No, if that's possible?

Thank you in advance for all your help!

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your Sheet1 and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Make a selection in B7.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Intersect(Target, Range("B7")) Is Nothing Then Exit Sub
    Select Case Target.Value
        Case "Yes"
            Sheets("Sheet2").Columns("EL:CN").Hidden = False
        Case "No"
            Sheets("Sheet2").Columns("EL:CN").Hidden = True
    End Select
End Sub

 

[Fluff]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address(0, 0) = "B7" Then
      Sheets("Sheet2").Columns("CN:EL").Hidden = Target.Value = "No"
   End If
End Sub

This needs to go in the sheet module with the drop-down

 

[VrRotate]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your Sheet1 and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Make a selection in B7.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Intersect(Target, Range("B7")) Is Nothing Then Exit Sub
    Select Case Target.Value
        Case "Yes"
            Sheets("Sheet2").Columns("EL:CN").Hidden = False
        Case "No"
            Sheets("Sheet2").Columns("EL:CN").Hidden = True
    End Select
End Sub

This worked perfect!! Thank you!!

 

[mumps]

Glad to help.

 

[VrRotate]

Glad to help.

One more quick question, if I wanted to populate the cells I'm hiding with a N is that possible?

 

[mumps]

Do you want the "N" to be entered only when the columns are hidden or when they are visible as well? Please clarify.

 

[VrRotate]

Do you want the "N" to be entered only when the columns are hidden or when they are visible as well? Please clarify.

Only when they are hidden.

 

[Fluff]

if I wanted to populate the cells I'm hiding with a N is that possible?

Which cells? Surely you do not mean every cell in all those columns

 

[VrRotate]

Which cells? Surely you do not mean every cell in the entire columns

Yes, it would be a N in every column - its a spreadsheet that gets uploaded to update data so it can't be blank.