How to address the current cell in conditional format custom formula?

Question

I need to write a conditional format rule with a custom formula, that should trigger when the certain cell's (the cell in the 3rd row of the column of the current cell) value is "TODAY()", and the current cell is empty. I know how to check the other cell, but is there a way to check the current cell's value in the same rule?

As you can see on this image, one column has a different color because the 3rd row of the column of the current cell contains the current date. And only empty cells are colored.

Here is my rule:

=and($3:$3=TODAY(), ????)

It should apply to all cells in a range A4:M10

I need it to be the one rule, not combination of multiple rules. I need to put something to the place of ????

In other words, I need to place the value described as "Cell is empty" in the custom formula as it's part.

Here is an example spreadsheet: https://docs.google.com/spreadsheets/d/1vpNrX2aUg8vY5WGDDuBnLfPuL-UyrjFvzjdATS73aq8/edit?usp=sharing

the 2 tables in your example look exactly alike on my google spreadsheet android app, not sure what is the problem — Aprillion, Feb 10 '16 at 08:20
I have a question not about Android, sorry. The difference is in the color of one column — Vladimir Mikhaylovskiy, Feb 10 '16 at 17:39
ah. i can see it now even in adroid. at the time the question was about non-empty cells which were all the same... will have a look later — Aprillion, Feb 10 '16 at 17:48

Aprillion · Accepted Answer · 2019-08-18T14:56:24.547

90

The current cell is addressed by the first cell of a range in the conditional formatting. In your example, the range is A4:M10 and therefore you can use A4 as "current cell".

Check for empty content:
```
=A4=""
```

Relative vs absolute references in conditional formatting work just like copying a formula.

Check that the cell in 2nd row of current column row is today:
```
=A$2=TODAY()
```
Combine using AND operator:
```
=AND(A$2=TODAY(), A4="")
```

I have updated a copy of your example spreadsheet - https://docs.google.com/spreadsheets/d/1MY9Jn2xpoVoBeJOa2rkZgv5HXKyQ9I8SM3kiUPR9oXU/edit#gid=0

edited Aug 18 '19 at 14:56

answered Feb 09 '16 at 19:43

Aprillion

21,510
5
55
89

1

Your google sheet has no read access to the conditional formatting. So I cannot see the formulas :( – moobi May 02 '23 at 08:44
@moobi no idea how to add permission for people to view it directly, please download the file and/or copy it to your own google drive – Aprillion May 03 '23 at 09:07

score 59 · Answer 2 · edited Aug 09 '18 at 12:49

59

If I want to check if current cell is empty this is working for me:

=ISBLANK(INDIRECT(ADDRESS(ROW(),COLUMN())))

The cell at the previous row in the column will be

=ISBLANK(INDIRECT(ADDRESS(ROW() - 1,COLUMN()))) etc.

edited Aug 09 '18 at 12:49

Kos

4,890
9
38
42

answered Aug 19 '17 at 10:58

Stanislav Pankevich

11,044
8
69
129

4

You will also need to enable "Iterative Calculations". Please go to File > Spreadsheet Settings and enable on **Calculation** tab **Iterative Calculation** – alexopoulos7 Jul 12 '18 at 10:47
1

I used `;` instead of `,` to separate parameters – piertoni Oct 15 '21 at 08:19

score 40 · Answer 3 · edited Aug 09 '18 at 12:50

40

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.

edited Aug 09 '18 at 12:50

Kos

4,890
9
38
42

answered Jan 12 '18 at 16:41

Markus Amalthea Magnuson

8,415
4
41
49

4

This looks like the most elegant way to use "this cell" in conditional formatting, I like it. However I cannot find anywhere that says using "RC" without any numbers will do what you predict. *It works* but I don't see it in documentation, including the document to which you link here! What made you even attempt it? – jay613 Aug 29 '19 at 19:03
3

I can't remember anymore and R1C1 is ancient in origin so I haven't found any formal specification. I probably found it while searching the web, here's an example of an article that mentions the "RC" method: https://powerspreadsheets.com/r1c1-formular1c1-vba/ ['…Finally, if you're referring to the cell itself (creating a circular reference), the first portion of the cell reference is simply “R”…'] – Markus Amalthea Magnuson Oct 27 '19 at 16:30

Vladimir Mikhaylovskiy · Answer 4 · 2016-02-10T21:28:20.163

6

Ok, I found the answer myself. The correct complete formula is:

=and($2:$2=TODAY(),INDIRECT("R"&ROW()&"C"&COLUMN(),FALSE)="")

This rule:

INDIRECT("R"&ROW()&"C"&COLUMN(),FALSE)=""

checks if the current cell is empty.

edited Feb 10 '16 at 21:28

answered Feb 10 '16 at 17:47

Vladimir Mikhaylovskiy

1,955
4
19
28

score 0 · Answer 5 · edited Aug 09 '18 at 12:50

0

Try apply to range:

A3:M10

Custom formula is:

=$2:$2=TODAY()

edited Aug 09 '18 at 12:50

Kos

4,890
9
38
42

answered Feb 10 '16 at 16:37

Ed Nelson

10,015
2
27
29

No, 3:3 will check only one cell, I need to check all cells in a range – Vladimir Mikhaylovskiy Feb 10 '16 at 17:39

score 0 · Answer 6 · answered Feb 20 '21 at 20:03

In your custom function rewrite the original conditional formatting range. The spreadsheet application will then take the current cell. As a result, your formula will be something like this

=function(A4:M10)

Apply transformations as necessary to make the result truthy/falsy.

How to address the current cell in conditional format custom formula?

6 Answers6