Google Sheets conditional formatting on whole row and cell

Question

Is there a way to use conditional formatting to change the whole color of a row besides a cell that you want another color?

For example: I want the whole row that says "scheduled" to be purple except two cells, one that states "not Submitted" and another that says "pending". I would like those two cells to be different colors.

Just wondering if there is some magical formula that will let me color the whole row minus cells that contain "...". and let me format those rows that contain "..." like I had before with the "text says exactly" rule.

Answer 1

You can set up three custom formulae, and arrange them so that the "not Submitted" and "pending" columns are evaluated first. For example:

=$B1="not Submitted" applied to range B:B

=$C1="pending" applied to range C:C

=$A1="scheduled" applied to range 1:1000 with purple background

See it working in this example sheet: https://goo.gl/bFJ8bu