CODEWITHSUNDEEP
google-sheetsconditional-formatting
Richard
Richard

Reputation: 37

Google Sheets Conditional Formatting on Individual Cells

I've tried searching low, high, and deep within the crevices of the internet to find a possible solution to this, but have had no luck. My main goal is to highlight individual cells based on a couple criteria. I have a google sheet that tracks jobs based on input from employees. My conditions are:

  1. Date must be before today
  2. Cell must be empty (no input from that day)
  3. Only the cell that is empty should be highlighted

https://docs.google.com/spreadsheets/d/10W9O55QQ31acOj5SyKcE0CXx8S78MZQtY-sRaIKgB7c/edit?usp=sharing

The goal of this is to make cells that did not receive any data stand out. The current formula I have is

=AND(ARRAYFORMULA(ISBLANK($B3:$D3)), ARRAYFORMULA(ISBLANK($I3:$K3)), $A3<TODAY())

But this is only highlighting the entire row, and when a cell in that row is filled, it no longer highlights that row. The yellow row is today's date. Any help in solving this is greatly appreciated!

Upvotes: 0

Views: 352

Answers (2)

iansedano
iansedano

Reputation: 6481

You need to set a conditional format rule for each column

I believe here are the conditional format rules you need:

Apply To Range Formula
A3:A =AND(OR(ARRAYFORMULA(ISBLANK($B3:$D3)), ARRAYFORMULA(ISBLANK($I3:$K3))), $A3<TODAY()) *Note the OR
B3:B =AND($A3<TODAY(), ISBLANK($B3))
C3:C =AND($C3<TODAY(), ISBLANK($C3))
D3:D =AND($D3<TODAY(), ISBLANK($D3))
I3:I =AND($I3<TODAY(), ISBLANK($I3))
J3:J =AND($J3<TODAY(), ISBLANK($J3))
K3:K =AND($K3<TODAY(), ISBLANK($K3))

The key concept here is that within one rule you cannot specify specific ranges to apply the rule to. Part of the definition of the rule is the range that it applies to. You can have rules that take precedence over certain ranges, but you cannot have two ranges in one rule. You can test many ranges in one rule, but the formatting applies to the whole range the rule is applied to.

Make sure you have deleted previous rules before applying these to make sure they don't take precedence

Reference

Upvotes: 0

Erik Tyler
Erik Tyler

Reputation: 9355

It's not 100% clear what should be happening with the merged Column E:H. But from the limited data and assigned colors I see in your sheet, try swapping out your current "pink" custom CF rule with this one:

=AND($A3<TODAY(), OR(AND(COLUMN(B3)<=4, B3=""), AND(COLUMN(B3)>=9, B3=""), AND(COLUMN(B3)>4, COLUMN(B3)<9, OR(B3<>"", JOIN("",$B3:$K3)=""))))

Upvotes: 0

Related Questions