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.