CODEWITHSUNDEEP
google-sheets
Henrik Söderlund
Henrik Söderlund

Reputation: 413

Apply conditional formatting to whole rows only if multiple rows render true?

I have an advertising campaign tracker that have rows for name, jobcode, startdate and endate. Multiple lines have the same job code, but each of them can have a different end date. I need to apply conditional formatting to all rows that have an enddate that has passed – but only if the end date has passed for all rows with the same jobcode. I have searched this forum and various places online without luck, I really would appreciate your advice on this.

I have created a sample sheet that you may look at. I have created a formatted sample of what I want to achieve at the bottom. Feel free to play on the top one.

Sample Spreadsheet

Thanks in advance!

Upvotes: 1

Views: 54

Answers (1)

Scott Holtzman
Scott Holtzman

Reputation: 27259

This formula will work when applied to A3:G8 (or further down the spreadsheet):

=countifs($D$3:$D$8,$D3,$G$3:$G$8,">="&today())=0

the $D in $D3 is to keep the formatting formula locked on column D as it applied across columns A:G.

Use this in the Custom Formula option of the Conditional Formatting

Upvotes: 2

Related Questions