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.
Welcome to Web Applications Stack Exchange. Consider sharing a publicly editable sample spreadsheet with realistic-looking data, and showing your hand-entered expected results there.– doubleunaryAug 18, 2021 at 16:21
Use this conditional formatting custom formula rule for the range
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. 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. Aug 21, 2021 at 19:15