In Google Sheets, is there a way to override other conditional formatting rules when the goal is to highlight the whole row?

user_15280337 0
Viewed 822

Here is a link to a copy of my data to mess around with. I have given permission to edit so you can see if it works.

What I want my data to look like this:

Thick borders represent where I would like purple (Missing) to highlight. This would be determined by the lack of due date in E:F

  • For a given course title (determined by column B), I want columns B:F to be highlighted in a specific color assigned to that class)
  • If due date (determined by E:F) is BLANK, AND assignment name (determined by text in column C) for a given row is not BLANK, then highlight the observation (row) in purple from C:F. This will help me know what I missed when I was hospitalized and there would be no due date because my professors just want me to turn it in.
  • If BOTH due date and course title are blank, I do not want any highlighting.

My problem is not point 1, but when I tried some formulas, it

I have tried:

= $C:$C = AND((NOT(ISBLANK($C:$C))), (ISBLANK(E:F)))
= $C:$C = AND((NOT(ISBLANK(C:C))), (ISBLANK(E:F)))
= $C:F = AND(NOT(ISBLANK(B2:B180)),(ISBLANK(E:F)))
= $C:$C = AND((ISBLANK($B:B)),(ISBLANK(D2:D)))
= $C:$C = AND(ISBLANK(E:F))
= $C:$C = IFS((ISBLANK(E:F)), NOT(ISBLANK($B:$B)))
= $C:$C = AND((ISBLANK(E:F)), NOT(ISBLANK($B:$B)))

Thank you in advance. I could look up someone else's template, but I am legitimately interested in getting better at solving problems like this.

conditional-formatting google-sheets
1 Answers

Is this what you're trying to achieve?

=AND((ISBLANK($E:$E)), ISBLANK($F:$F), NOT(ISBLANK($C:$C)))

If you put this rule higher in the list of conditional formatting rules, it will take precedence over the rules that color the cells according to course title.

user_137350730
Related