27

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?

Improve this question
2

3 Answers 3

Reset to default
16

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.

Improve this answer
1
  • the question was for retrieving "this cell" in conditional formatting. @markus-amalthea-magnuson has the right answer which is dependent upon knowing the "notation" ahead of time (which is impossible when inside conditional formatting since it must fit a entire range)
    – WEBjuju
    Jan 11, 2022 at 21:23
10

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.

Improve this answer
1
  • 1
    that's not documentation (i mean, your link points to what they think is documentation, but w/o this SO solution, nobody would ever know that this is the solution)
    – WEBjuju
    Jan 11, 2022 at 21:21
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.

Improve this answer
1
  • $ does not mean this here
    – törzsmókus
    Sep 13 at 13:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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