Hide row & columns with particular cell value

[A. Karatas]

I have a couple of worksheets in the same workbook who needs to show
columns R:T and row 15 if the cell AE1 changes in 1 or 2 triggerd by a
formula by a dropdown box in the mainsheet (input). The following code
doesn't work. Why??

Sub DropDown_Change()

'hide or unhdide columns and row
Dim sh As Worksheet
For Each sh In Worksheets
If sh.Name <> "input" Then
If sh.Range("Ae1").Value = 2 Then
Columns("P:R").Select
Selection.EntireColumn.Hidden = True
Rows("15:15").Select
Selection.EntireRow.Hidden = True

ElseIf sh.Range("Ae1").Value = 1 Then
Rows("15:15").Select
Selection.EntireRow.Hidden = False
Columns("p:r").Select
Selection.EntireColumn.Hidden = False
End If
End If
Next
End Sub

 

[Mike H]

Try this:-

Sub DropDown_Change()

'hide or unhdide columns and row
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Select
If ws.Name <> "input" Then
If ws.Range("AE1").Value = 2 Then
Columns("P:R").Select
Selection.EntireColumn.Hidden = True
Rows("15:15").Select
Selection.EntireRow.Hidden = True

ElseIf ws.Range("AE1").Value = 1 Then
Rows("15:15").Select
Selection.EntireRow.Hidden = False
Columns("p:r").Select
Selection.EntireColumn.Hidden = False
End If
End If
Next

End Sub


Does that work?
Mike

 

[A. Karatas]

it debugs on the line

ws.select

 

[Mike H]

It doesn't on my machine. Have you copied all my code because I made a few
changes to yours.

Mike

 

[A. Karatas]

It really debugs. No matter what I do. I copied the macro into a
module.

If I take out the line the macro only works in the sheet "input'

 

[Mike H]

Hi,

I'm afraid I'm at a complete loss to understand why it doesn't work, it
works perfectly on a second machine i've tried.

Mike