The formula for the first part of the query is in the yellow cell.

Conditional formatting deals with the rest so highlight one of the other cells and check conditional formatting to see how it's done.

S.