# Conditional Formatting if all cells fail to meet a certain criteria

This is the spreadsheet I'm working on. I am trying to figure out a formula for conditional formatting so that it only highlights the cells in red if none of the cells from columns N to Y of the Failed tab says either "Yes, I take this medication within the specified dosage range" or "I am unsure If I take this".

• What column should be highlighted in this case? All columns N-Y or something else?
– user79865
Commented Jul 11, 2016 at 12:53
• columns N-Y should be highlighted; none if the condition is met
– Vee
Commented Jul 11, 2016 at 12:59

After selecting the range N2:Y, apply to it conditional formatting with "Custom formula is:"

``````=sum(arrayformula(n(regexmatch(\$N2:\$Y2, "Yes, I take this medication within the specified dosage range|I am unsure If I take this")))) = 0
``````

Explanation: `\$N2:\$Y2` refers to the range N-Y in the current row. `regexmatch(\$N2:\$Y2, "this|that")` returns True when the cell contains either "this" or "that" (this is a substring search, which is appropriate in your case). Then `N` converts True to 1 and False to 0. The whole array of these 1-0 values is then summed, and the formatting applies if the sum is 0, meaning there were no matches.

• Thanks @Bookend. The formula works on non-empty cells but the empty cells are formatted in red because they met the criteria too. How do I get around that?
– Vee
Commented Jul 12, 2016 at 3:53
• You can exclude empty cells by changing the formula to =`len(N2)*(sum(...) = 0)` where sum(...) is as above
– user79865
Commented Jul 12, 2016 at 4:37