15

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?

8

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

=C1 < today() - 14

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:

=and(C1 < today() - 14, row(C1) >= 8)

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.

1

This is the shortest possible way I've found to reference the current cell in conditional formatting spanning a range: INDIRECT("RC",FALSE). Documentation is here.

0

Based on @user79865's answer, we need to refer to this cell with a $ sign. For example,

  1. Custom role of when the cell's value is "Yes":

    =$C1="Yes".
    
  2. Custom role of when the value is an odd number:

    =(Int($C1/2)-$C1/2)<>0
    

More examples here.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.