Apply conditional formatting based on the formula that a cell contains

Question

I am having trouble figuring out how to get conditional formatting to work in my Google spreadsheet. I use the second sheet to import prices of projectors, but a few don't update.

I want the row of prices to turn green when there is the LEFT function in the cell such as =LEFT('Import Page'!B4,7), but when I try to get the conditional formatting to work using the "cell contains" "left" formatting it does not work.

Here is the image

Answer 1

What you are attempting to do is impossible. Google Sheets functions reference only the computed value, not the underlying function text. Therefore, if your conditional formatting rule says that it should be green if it includes the text "left", it will read your cell, see that it contains only "$529.91," and conclude that the rule should not apply.

Answer 2

As davidseibert explained, conditional formatting cannot detect whether a cell contains =left function. The best it can do is to detect that the cell contains some function: for this you would just format the range B2:Z2 using custom formula =isformula(B2).

With an Apps Script

Here is an Apps Script function that paints green all cells of the second row of the current sheet containing left in a formula:

function highlightLeft() {
  var range = SpreadsheetApp.getActiveSheet().getDataRange().offset(1,0,1);
  var formulas = range.getFormulas();
  for (var j = 0; j < formulas[0].length; j++) {
    if (/left/i.test(formulas[0][j])) {
      range.getCell(1, j+1).setBackground('LightGreen');
    }
  }
}

To enter this, go to Tools > Script Editor. To run this function once, the "run" button of script editor will do.

Or, if you need to change formatting with subsequent edits, a function like this will do:

function onEdit(e) {
  if (e.range.getSheet().getSheetName() == 'Comparison' && e.range.getRow() == 2) {
    if (/left/i.test(e.range.getFormula())) {
      e.range.setBackground('LightGreen');
    }
    else {
      e.range.setBackground('white'); 
    }
  }
}

This watches the edits to the second row of the sheet named "Comparison"; if the edited cell has a formula with "left", the background is light green, otherwise white.