0

In Google Sheet I would like to highlight in red the cells in a column "Y" if another column, say column "X", contains certain abbreviation (string variable). Column "X" cells can have only one abbreviation, i.e. "BBL", "BBS", "BVV" or "BBD". So my goal is that each time we see "BBL" or "BVV" in "X" to have red colour in "Y". Additional issue may be that "Y" is a data validated field, and valuse that can be chosen there do not depend on column "X", but I assume that colouring of cells with conditional formatting does not interact with data validation.

enter image description here

Improve this question
1

1 Answer 1

Reset to default
0

Use this conditional formatting custom formula rule for the range Y2:Y:

=regexmatch(X2, "^(BBL|BBV)$")

Improve this answer
2
  • I have used the solution suggested but I get the invalid formula warning. I could think that this is due to the ownership of the sheet, because when I used the same solution in another sheet (with me as an owner) with a smaller sample of the data, this works just fine. What could be another possible reason, if not the ownership of the sheet. Maybe helpful: when I remove the "=" sign from the formula the "invalid formula" warning disapears, but this does not help of getting the expected result in the column, no cell gets coloured.
    – Zan Ilieski
    Aug 19, 2021 at 9:39
  • In the event your spreadsheet is in a locale that uses semicolons as formula argument separators, replace the comma , in the formula with a semicolon ;. Test the formula in a cell rather than in a conditional formatting rule to confirm that it works OK.
    – doubleunary
    Aug 21, 2021 at 19:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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