CODEWITHSUNDEEP
google-sheetsgs-conditional-formatting
Vladimir Mikhaylovskiy
Vladimir Mikhaylovskiy

Reputation: 2185

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

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

Upvotes: 105

Views: 150213

Answers (7)

mayo
mayo

Reputation: 4075

Adding my two cents;

Another way to check that the current cell is different from the cell in the previous column;

=INDIRECT("RC",FALSE) <> OFFSET(INDIRECT("RC", FALSE), 0, -1)

Upvotes: 0

Dragas
Dragas

Reputation: 1311

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.

Upvotes: 0

Aprillion
Aprillion

Reputation: 22304

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

Upvotes: 104

Ed Nelson
Ed Nelson

Reputation: 10259

Try apply to range:

A3:M10

Custom formula is:

=$2:$2=TODAY()

Upvotes: 0

Markus Amalthea Magnuson
Markus Amalthea Magnuson

Reputation: 8741

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.

Upvotes: 51

Stanislav Pankevich
Stanislav Pankevich

Reputation: 11368

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.

Upvotes: 66

Vladimir Mikhaylovskiy
Vladimir Mikhaylovskiy

Reputation: 2185

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.

Upvotes: 6

Related Questions