VB macro question in Excel?

Question

I have a spreadsheet that has multiple tabs (Day 1, Day 2, up to Day 14), I would like to draft a macro that looks to see the value of a cell ('Main Page'!D5) to see what Day is selected, and based off of that go to that day as well as all subsequent days and hide rows 13-16 and rows 24-40.

Please help, I don't want to have to do separate macros for each individual day.

Answer 1

Put the following code in Main Page module (Alt+F11, left side double click icon with word "Main Page", on the right side put code)

Private Sub Worksheet_Change(ByVal Target As Range)

startDayCell = "D5" 'cell determine which day to start

maxDay = 14 'max no. of day

hideRange = "$13:$16,$24:$40" 'range to hide

hideOrShow = 1 '1 to hide, 0 to show

If Not Intersect( Target, Range( startDayCell)) Is Nothing Then

   stday = Range( startDayCell)

   If stday > maxDay Or stday <> Int( stday) Or stday < 1 Then Exit Sub

   For a = stday To maxDay

      Sheets( "Day " & a).Range( hideRange).EntireRow.Hidden = hideOrShow

   Next

End If

End Sub

Then whenever you enter an integer from 1 to 14 in D5 of Main page tab, those sheets with day no. >= the integer will have the specified range hidden

Please contact for more details