For a field I have conditional formatting with custom formula: =today()>C8+14 and =today()>C8+30 with different styling, basically I want to have a visual styling to highlight older and oldest stuff, two weeks and month. This works. Column C have a date, row can be any row N, so C$N does not help.
However the problem is that I have new rows all the time and it would be easier just to copy-paste the field with rules, and change the date. Rules, however stay as referring to the original here C$N cell.
Could I replace the specific C$N with a this(), self() or is there something like that – to make it more generic copy/pasteable conditional formatting?
Best Answer
Generally: the way to refer to "this" cell is to enter notation for the upper-left corner of the range being formatted. For example, if the range is C1:C, then C1 means "this cell".
For example, formatting C1:C with custom formula
will format all cells in C with dates two weeks in the past.
If the range you want to format begins with row 8, and you think you may be inserting rows above that (thus shifting the range), then this formula can be used:
The formatting will apply only to rows starting with 8, but the range being the entire column, the formula will handle insertion of rows above row 8.