Simplifying Conditional Formatting Rules (what I have works, it just requires a LOT of rules)

Question

Hi all!

I have an attendance tracker which covers a school morning and afternoon check-in everyday Mon-Fri for the entire 11-week term and need a hand simplifying Conditional Formatting rules.

Example

I've simplified the actual tracker and shortened it to just two weeks as an example here and pictured below.

Here's how it works:

• Using checkboxes and COUNTIFS formulas, it automatically tallies how many primary and secondary students attended.
• Using macros, it has 'Select All' and 'Deselect All' functionality for each column (which I've removed for security in the example spreadsheet).
• Using Conditional Formatting, it changes the colours of the columns according to the current day and time to make it very easy to know which column to enter data in. By having the 'current' column coloured differently, it eliminates needing to know what the current date is or searching though a long spreadsheet to find it, and makes it very obvious if you're entering in the wrong column.

How the Conditional Formatting works:

Since there is a check-in in the morning and another in the afternoon, we have an 'AM' and 'PM' column for each day (AM is midnight to midday and PM is midday to midnight). As I mentioned above, the colours of the columns change. Here's how it works:

• The column for the current date and time is green. This is the column the user will edit.
• Any columns in the past (to the left) are blue so the user can reference previous attendance if needed.
• Any columns in the future (to the right) are white so they appear 'hidden'. Note: The 'Select All' boxes need to a blue-ish shade to appear 'hidden' and are controlled separately (we'd like to keep them blue).

So for example, if it's currently February 11th at 3pm, the 'PM' column for February 11 will be green. Every column to the left of that (in the past, and including February 11th's AM column) will be blue and any column to the right (in the future) will be 'hidden' in white. Refer to the screenshot above which shows this scenario.

You can easily manipulate the day and time the spreadsheet uses by going to Cell B5 in the 'Brains' sheet. Try manipulating it to see how the spreadsheet reacts for each day and time.

My Problem:

As you can see if you've manipulated the day and time, the Conditional Formatting I've used works a treat!

However, I'm sure the way I've done it can be improved. The problem is the sheer number of rules I've used to do it; there's three each for AM and PM for every day! That's over 300 rules for the full 11-week term! I'm pretty sure the 90-odd rules for just three weeks was causing some lag in the workbook at times, so I'd hate to think how it will respond when more than tripled to over 300! And having so many rules will make any future edits (ie. extending the tracker for more students) very tedious as each rule would have to be manually changed.

If you open the Conditional Formatting Manager and change it to show rules for the entire worksheet, you'll see all the rules.

How the Rules Work:

Below is a screenshot of the AM rules for Monday of Week 1:

• Rule 1 (top): This does nothing. It's simply my way of organising and naming the rules for each day.
• Rule 2: This will colour all of the checkboxes from Row 4 to Row 47 (including the Select All boxes) in blue if the date of that column is 'less than' (in the past) of the current date/time in Cell B5 on the 'Brains' sheet. The '+0.5' in the formula is used to separate AM from PM.
• Rule 3: This will colour the 'Select All' box in Row 5 blue so it looks hidden if the date of that column is 'greater than' (in the future) of the current date/time in Cell B5 on the 'Brains' sheet (same as Rule 4 below, except it needed to be a different colour and hence have a separate rule).
• Rule 4 (bottom): This will colour all the checkboxes white, except the 'Select All' ones, if the date of that column is 'greater than' (in the future) of the current date/time in Cell B5 on the 'Brains' sheet.

The PM rules work much the same, except contain '+1' or '+0.5' to add 12 hours onto the date at the top of the column so they only work in the PM half of the day.

Below is a screenshot of the PM rules for Monday of Week 1:

Is there a way to use fewer Conditional Formatting rules but have the same functionality?

I'm thinking somehow use one rule for each colour change that is able to recognise and change the colouring of the columns according to the date above it?

Or, if the way I've done it is the only way it will work - how can I make it autmatically extend and create new rules when I extend the worksheet without having to manually type them? Does removing the '$' do this? I experiemnted briefly with it and it seemed to put the '$' back after I removed them!

Thank you for any advice on this!