I want to hide a certain number of rows on one sheet, and columns in another, based on the number in a certain cell. I have 30 rows labeled as 1-30 in A11:A40 on Sheet1 and 30 columns on Sheet2 labeled as 1-30 in F9:AI9. When you enter a number in a certain cell (e.g. "7" in cell C8), I want it to hide rows 8-30 on Sheet1 and columns 8-30 on Sheet2.
This works for Sheet1, but I don't know how to reference another sheet or have it simultaneously apply to columns:
Sub Hide_Rows_Columns()
Dim c As Range
For Each c In Range("A11:A40").Cells
If c.Value > Range("C8") Then
c.EntireRow.Hidden = True
End If
Next c
End Sub
Also, can this work just by entering the value in C8 and not having to press run on the macro?
This works for Sheet1, but I don't know how to reference another sheet or have it simultaneously apply to columns:
Sub Hide_Rows_Columns()
Dim c As Range
For Each c In Range("A11:A40").Cells
If c.Value > Range("C8") Then
c.EntireRow.Hidden = True
End If
Next c
End Sub
Also, can this work just by entering the value in C8 and not having to press run on the macro?