Conditionally format a cell based off a selection of drop down data in a column

Question

I created an apparatus maintenance board for my fire department. Each apparatus has its own tab with the name of that apparatus in cell A1.

Each row below that has room to list issues for that apparatus with column D being a drop-down list for the current progress. I want to be able to format the color of the apparatus name is cell A1 based on multiple values from the drop-down list in column D.

Example, when a maintenance issue is not completed we select from the drop-down list in column D of either SERVICE SCHEDULED, PENDING, PARTS ORDERED, etc. I want the apparatus name in A1 to turn red if any cell in column D contains one of those statuses.

Answer 1

With A1 selected, go into Conditional Formatting and Custom formula is.. and enter

=SUMPRODUCT(countif(D:D,{"SERVICE SCHEDULED", "PENDING", "PARTS ORDERED"}))

and select red fill colour.

Answer 2

custom formula:

=COUNTIF(D:D,{"SERVICE SCHEDULED", "PENDING", "PARTS ORDERED"})

Answer 3

Slightly longer than Tom Sharpe's answer (and slightly less easily extensible) but possibly a little simpler since applying only a single function this formula:

=ArrayFormula(or(D:D="SERVICE SCHEDULED",D:D="PENDING",D:D="PARTS ORDERED"))

with choice of red font might suit.