Conditional Formatting & Audits
Highlight Cells Matching Any Value in a Watchlist Range (COUNTIF Membership Rule)
Replace brittle 10-way OR formulas with a table-driven COUNTIF($D$2:$D$5, TRIM(A2)) > 0 rule that highlights any row matching an editable watchlist or priority code table.
Why a range-driven COUNTIF rule scales better than OR(A2="X", A2="Y", ...)
Hardcoding OR(A2="DE", A2="FR", A2="NL", ...) inside a conditional formatting dialog hides business logic from reviewers and requires editing the rule every time a country or priority SKU changes.
Testing COUNTIF($D$2:$D$5, TRIM(A2)) > 0 against an explicit watchlist range on the sheet turns membership highlighting into transparent data: adding or removing a code in D2:D5 updates every highlighted row immediately.
| Order Country (A) | Watchlist ($D$2:$D$4 = BG, DE, NL) | COUNTIF > 0 Flag |
|---|---|---|
| "DE" | 1 match | TRUE (Highlight) |
| "US" | 0 matches | FALSE |
| " nl " (padded lowercase) | 1 match after TRIM | TRUE (Highlight) |
| (Blank cell) | Guarded by NOT(ISBLANK) | FALSE |
Watchlist Membership Highlight Rule
Checks whether trimmed A2 appears at least once in locked watchlist $D$2:$D$4 while ignoring empty cells.
=AND(NOT(ISBLANK(A2)), COUNTIF($D$2:$D$4, TRIM(A2)) > 0)Inverted Non-Allowlist Exception Rule
Flipping > 0 to = 0 flags unauthorized or unmapped codes that are NOT in the approved reference list.
=AND(NOT(ISBLANK(A2)), COUNTIF($D$2:$D$4, TRIM(A2)) = 0)Try the example
Use the original values or change them to test your understanding.