With the assumption, the days are actual dates custom formatted as
d you can do the following:
- In my case, below, select range
New conditional formatting rule:
Apply a formatting and confirm. Result as below.
Beware, this is volatile (since all custom formatting is volatile, as per @teylyn her comment), but since it looks like your worksheet won't be heavy on calculation anyway, you should be fine.
You asked me, how does this work? We will have to break it down in parts:
OFFSET function will return the value from a cell that is N rows and N columns offset from a referenced cell. This is our main function here.
OFFSET needs a reference address from which you want to actually offset from. I used
ADDRESS function, which should return an address.
ADDRESS needs at least a row and column, which we gave the formula with
COLUMN(), which both look at the row and column of it's current cell (makes sense in conditional formatting since the formula will apply to all cells). The rest of the parameters are optional and we can leave them out.
- We use
INDIRECT() to actually return the valid address.
- The second important piece of the
OFFSET function is to feed it a rownumber to offset with. I used
MOD() which will return, in this case, an integer of the remainder of two numbers after division. e.g cell
C10 will return
- The same trick we do with the column parameter,
-0. Notice how in both parameters we used the
- to create a negative offset?
- The above will create an offset for each cell redirecting to it's appropriate corresponding date. Which we simply evaluate against
TODAY(). Anything in the past will fit our rule and have conditional formatting.
Hope that makes sense :)