You want last week's rows to disappear all at once every Monday (Sunday night at 11:59 ideally). You use formatting (and possibly conditional formatting) within your "calendar" (for example, 24 December is highlighted in green).
There are likely to be many ways in which your question could be answered. I have prioritised three factors:
1 - Any/all data formatting must be included in the output
2 - No on-going maintenance
3 - Avoid a script unless completely necessary.
You suggest that conditional formatting might be part of a solution. Perhaps it might, though I am sceptical; in any event, I will leave that for someone else to explore.
The answer requires some modification to the spreadsheet.
- Unmerge cells A1, B1, C1, D1, E1, F1 and create the layout and content as shown in the image and here:
- Cell A1:
- Cell B1:
- Cell C1: 'Week->'
- Cell D1:
- Column A, CellA3:
- copy this down as many rows of data in the calendar. If you add new rows, include this cell when you drag the last row to create new rows.
- Create a filter:
- select the range
A2:F1000 - IMPORTANT: extend the depth of the range to the last row of the spreadsheet OR as many rows as you think that your calendar will occupy.
- From the main menu, select
Data, Create a filter.
- Click the
Filter icon in Cell A2.
Filter by Condition
Custom Formula is
- Click OK
Filter - Sample Snapshot
My sample data runs from week 49 to week 52.
In the snapshot, I have "forced" an earlier "current" date to demonstrate how the filter drops off earlier weeks and displays later weeks.