26

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

4 Answers 4

Reset to default
17

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
    Commented Jan 11, 2022 at 21:23
13

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
2
  • 2
    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
    Commented Jan 11, 2022 at 21:21
  • perfect, thank you!
    – FurloSK
    Commented Sep 8 at 11:03
1

You can use following combination of functions.

INDIRECT(ADDRESS(ROW(),COLUMN())

You can write complex logics with it.

E.g., Check if this cell's value is equal to this month, and the right cell's value is greater than date of month.

=AND(EQ(INDIRECT(ADDRESS(ROW(),COLUMN()),MONTH(TODAY()),GT(INDIRECT(ADDRESS(ROW(),COLUMN()+1),DAY(TODAY()))
Improve this answer
-1

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
    Commented Sep 13, 2023 at 13:58

Your Answer

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

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