1

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 screen shot - conditional formatting

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.

  • There is one exception, namely isformula. It can be used to highlight cells that contain a formula of any kind; however it does not say what the formula is. – user79865 Jan 2 '16 at 5:54
0

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.

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.