CODEWITHSUNDEEP
google-sheetsgs-conditional-formatting
W M
W M

Reputation: 7

Can you set up conditional formatting based on colors of other cells?

I'm a projectionist at a movie theater, and we have a spreadsheet to track all of our content chronologically. Each show has its own row, complete with all of the details regarding time, theater number, format (digital DCP, Blu-ray, 35mm, etc).

We use this to verify at-a-glance where we're at on managing the content to make sure everything is set up to go. I have conditional formatting set up in every cell so that if it's a Yes or - (meaning not applicable to that show) it turns green, and if it's a No or ? (meaning we haven't gotten that information yet) it turns red.

What I want to know is if it's possible to have another column specifically for an overall "Yes" or "No", based automatically on the contents of the rest of the row (for example, column "D" will be green for yes and red for no). So if a show has all of its criteria met for the rest of the row (E* through Z*), manually filled out to turn green, that one cell (D*) will automatically follow suit. If something is missing from the rest of the row and contains even one red cell (anywhere in E* through Z*), that one cell (D*) will stay red as well.

I realize it probably needs a bit of scripting involved for this, which is not within my realm of experience, but I'm not even sure if it's possible. A lot of the spreadsheet is inputted manually, so one more column with a manual cell isn't the most inconvenient, but if we could set it up to be automatic, that would be incredible.

Upvotes: 0

Views: 787

Answers (1)

dwirony
dwirony

Reputation: 5450

To branch off my comment above:

Using AND

Additionally, have the cells by default set to red (using the fill option). If any of the cells are "NO", the condition won't be met, and it'll show as red:

Red

Using OR:

=AND(OR(A1="YES",A1="-"),OR(B1="YES",B1="-"),OR(C1="YES",C1="-"))

Using Or

Upvotes: 1

Related Questions