1

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 Jul 11 '16 at 12:53
  • columns N-Y should be highlighted; none if the condition is met – Vee Jul 11 '16 at 12:59
2

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 Jul 12 '16 at 3:53
  • 1
    You can exclude empty cells by changing the formula to =len(N2)*(sum(...) = 0) where sum(...) is as above – user79865 Jul 12 '16 at 4:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.