Conditional Formatting & Audits
Highlight Entire Rows by Day of Week or Incomplete Required Fields
Apply column-locked row formatting rules using WEEKDAY($B2, 2) = $G$2 to spotlight Friday/Monday cutoffs and AND(COUNTA($A2:$D2) > 0, COUNTBLANK($A2:$D2) > 0) to catch half-entered rows.
Column-locked $B2 mixed references and avoiding blank-table highlighting
To shade an entire row A2:D2 based on one date cell or row completeness test, lock the column with $ ($B2, $A2:$D2) while keeping row 2 relative so every column in row r evaluates the exact same row-level test.
For missing-field row audits, bare COUNTBLANK($A2:$D2) > 0 highlights every empty row below your data! Pairing COUNTA($A2:$D2) > 0 with COUNTBLANK($A2:$D2) > 0 highlights ONLY partially populated rows where someone started a record and left a required cell blank. In shared Arte Sheets schedules, reviewers with Suggest-only permissions or @mention comments can flag these incomplete rows directly for owners to complete.
| Shipment Row | Ship Date (ISO Weekday) | Target Day ($G$2 = 5 Fri) | Target-Day Flag | Incomplete-Row Flag |
|---|---|---|---|---|
| SHP-01 (Complete, Fri May 8) | 5 (Friday) | 5 | TRUE | FALSE |
| SHP-02 (Missing Carrier, Mon May 4) | 1 (Monday) | 5 | FALSE | TRUE (1 blank field) |
| SHP-03 (Complete, Mon May 11) | 1 (Monday) | 5 | FALSE | FALSE |
| Row 5 (All 4 cells blank) | — | 5 | FALSE | FALSE (Ignored) |
Highlight Entire Rows Falling on a Specific ISO Weekday
Uses return_type 2 (Monday = 1 .. Friday = 5 .. Sunday = 7) with ISNUMBER($B2) so blank rows never evaluate as Saturday (serial 0).
=AND(ISNUMBER($B2), WEEKDAY($B2, 2) = $G$2)Highlight Partially Entered Rows Missing Required Fields
Flags rows that have at least one populated cell (COUNTA > 0) and at least one missing cell (COUNTBLANK > 0).
=AND(COUNTA($A2:$D2) > 0, COUNTBLANK($A2:$D2) > 0)Try the example
Use the original values or change them to test your understanding.