Script to Change Row Color when a cell changes text
Realise this is an old thread, but after seeing lots of scripts like this I noticed that you can do this just using conditional formatting.
Assuming the "Status" was Column D:
Highlight cells > right click > conditional formatting. Select "Custom Formula Is" and set the formula as
=RegExMatch($D2,"Complete")
or
=OR(RegExMatch($D2,"Complete"),RegExMatch($D2,"complete"))
Edit (thanks to Frederik Schøning)
=RegExMatch($D2,"(?i)Complete")
then set the range to cover all the rows e.g. A2:Z10
. This is case insensitive, so will match complete, Complete or CoMpLeTe.
You could then add other rules for "Not Started" etc. The $ is very important. It denotes an absolute reference. Without it cell A2 would look at D2, but B2 would look at E2, so you'd get inconsistent formatting on any given row.
//Sets the row color depending on the value in the "Status" column.
function setRowColors() {
var range = SpreadsheetApp.getActiveSheet().getDataRange();
var statusColumnOffset = getStatusColumnOffset();
for (var i = range.getRow(); i < range.getLastRow(); i++) {
rowRange = range.offset(i, 0, 1);
status = rowRange.offset(0, statusColumnOffset).getValue();
if (status == 'Completed') {
rowRange.setBackgroundColor("#99CC99");
} else if (status == 'In Progress') {
rowRange.setBackgroundColor("#FFDD88");
} else if (status == 'Not Started') {
rowRange.setBackgroundColor("#CC6666");
}
}
}
//Returns the offset value of the column titled "Status"
//(eg, if the 7th column is labeled "Status", this function returns 6)
function getStatusColumnOffset() {
lastColumn = SpreadsheetApp.getActiveSheet().getLastColumn();
var range = SpreadsheetApp.getActiveSheet().getRange(1,1,1,lastColumn);
for (var i = 0; i < range.getLastColumn(); i++) {
if (range.offset(0, i, 1, 1).getValue() == "Status") {
return i;
}
}
}