Since I have spent a substantial amount of time figuring out how conditional formatting works, as well as discovering that it’s quite different from normal / fixed formatting, I thought it good to share this info here. Maybe the powers that be will find it in their hearts to add some form of this to the documentation which lacks the detail in this respect wrt conditional formatting.
I want to format a row/range in a sheet a different shade when the date matched today’s date. So I use a formula in conditional formatting to set this:
Example:
Set formula to $A$2=INT(NOW()) and set the accent to “Good”.
Set the range to A2:F2.
This works fine for that single line. When I copy and paste this line to expand the sheet, the conditional formatting is expanded to encompass the additional range although I don’t want that.
Suppose I copy A2:F2 to A11:F11, I expect a new condition to be created, but instead the existing condition gets an expanded range: A2:F2;A11:F11, but the formula to evaluate stays.
Of course, all the included ranges change to “good” accent when the date matches today.
Of course the formula has an absolute reference, $A$2. So one would think that you remove the absolute reference it will create a new range relative to existing one. Change $A$2 to A2. That results in only the date field, A2, getting conditional formatting. So change A2 to $A2. (to me that means: always evaluate column A, but adjust for rows that that may be copied in future). It now applies the conditional formatting to A2:L2 correctly.
Now copy A2:L2 to a new row.
Is seems to be working. If A2 is today’s date, then A2:L2 are “good” formatted. If A11 is today’s date, then A11:F11 are “good” formatted.
Looking at the conditional formatting that was created, it’s becomes weird.
The range has been expanded (not a new range created), but the formula is still $A2.
If one uses the format painter, a similar result is achieved.
I have been told before that I don’t understand the logic of absolute and relative formatting. Clearly a different kind of logic has been applied here, but at least it’s predictable.
So if you have battled like I did to make sense of this, then here’s your answer. It is a strange way in which it is implemented, but once you figure out how it works, it actually works! 
