In Excel (2007), how can a range of cells with Conditional Formatting (CF) be copy/pasted into another range of cells in a way that RELATIVE CF is transferred?
As an example, suppose you want to create a full-year calendar beginning with the current week as shown in Fig. A,
in which dates are calculated as shown in Fig. B
(except Sundays which are calculate from the previous Saturday)
Also, suppose you want CF applied so that the current day will automatically change to a yellow fill. This can be done by setting a separate cell (like A1) to
and then applying CF as shown in Fig. C
which produces the result shown in Fig. D
Now, the question is how can that same CF be applied to the other days in the calendar? If the CF day (the 23rd) is simply copy/pasted to another day (the 24th), you end up with the situation shown in Fig. E
where, because the original reference in the 23rd is absolute ($G$3), now the 24th lights up also. Thus, although the relative formula for the date is OK (showing the value, 24), the CF for all the cells in the range comprising the 24th is actually tied to the 23rd.
Alternatively, if you go back and change the initial CF to a relative reference (Fig. F),
then it only formats the upper-left cell in the range (Fig. G).
Therefore, aside from doing it by hand, how can something the size of this calendar be CF, let alone, something many times larger?