Google-sheets – How to make the “Text contains” evaluate multiple values in Google Spreadsheet

conditional formattinggoogle sheets

I want the cell to be formatted if the cell contains any of the following words: blue, orange, red, green, and white. I also want to not make 5 different commands for each (this is because I have a loooong list in my actual file).

The entry blue|orange|red|green|white does not seem to work; nor does the entry blue OR orange OR red OR green OR white. How do I do this?

Best Answer

IMO better without "Text contains".

To avoid the possibility of conflicting rules, clear all conditional formatting from ColumnA (assumed because OP does not say where the data is), select it and apply a Custom formula is of:

=regexmatch(A1,"blue|green|orange|red|white")

Select your formatting and Done.

This will format blue-green as well as blue and Red car as well as red, but also coloured.

If to format only cells that contain the selected colour names (and nothing else, not for example White paper), try:

=match(A1,{"blue","green","orange","red","white"},0)

For your locale you might need ; rather than ,.

REGEXMATCH.

MATCH.

Related Solutions

Google-sheets – Date Formatting for Deadlines

For Dates more than 7 days from today: Black I am assuming (i) more than 7 days in advance of today and (ii) black font is suitable – so no conditional formatting is necessary other than that applied elsewhere.

For Dates less than or equal to 7 days from today: Green I am again assuming in advance of and suggest select column, Format, Conditional formatting…, Custom formula is:

=and(A1<today()+7,A1>=today())

with Background Colour: ticked and green chosen.

For Dates before today (late projects): Red select column, Format, Conditional formatting…, Custom formula is: as before and:

=and(A1<>"",A1<today())

with Background Colour: ticked and red chosen.

Google Sheets – Change Cell Color in a Range

Use the following code to accomplish your goal.

Code

function onOpen() {
  SpreadsheetApp.getUi().createMenu('Custom Format')
    .addItem('Max & Color', 'getmaxColor').addToUi();
}

function getmaxColor() {
  var ss = SpreadsheetApp.getActive(), aRange = ss.getActiveRange(); 
  var data = aRange.getValues(), colors = aRange.getBackgrounds();
  var dest = aRange.offset(0, data[0].length, 1, 1);
  var index = getmaxIndices(data);
  var max = data[index.i][index.j], color = colors[index.i][index.j];
  dest.setValue(max).setBackground(color);  
}

function onEdit() {
  var ss = SpreadsheetApp.getActive(), col = ss.getActiveCell().getColumn();
  if(col < 7) {
    var aRange = ss.getRange("A1:F7"), data = aRange.getValues();
    var colors = aRange.getBackgrounds();
    var dest = aRange.offset(0, data[0].length, 1, 1)
    var index = getmaxIndices(data);
    var max = data[index.i][index.j], color = colors[index.i][index.j];
    dest.setValue(max).setBackground(color); 
  }
}

function getmaxIndices(data) {
  var max = 0, indexI = 0, indexJ = 0;
  for(var i=0, iLen=data.length; i<iLen; i++) {
    for(var j=0, jLen=data[0].length; j<jLen; j++) {
      if(data[i][j] > max) {
        max = data[i][j], indexI = i, indexJ = j;
      }
    }
  }
  return {"i":indexI, "j":indexJ};
}

Explained

The onOpen() adds a menu item. Select the range you want to evaluate and select the menu option Max & Color. The values and backgrounds are collected as is an offset range. Then the indices (2d-array) of the max value are obtained, through the getmaxIndices(). Afterwards the corresponding max value and color is set.

The onEdit() does almost the same, but it has a fixed range. It will only fire if a cell is edited and if the edited cell belongs to the A1:F7 range.

Note

The reason for getting the error message, can be read in this answer: custom function

Example

I've added the sample code into your example file.