I am currently in the progress of working on something for Vex Studios and Iā€™m using Google Docs so that we can collaborate on the list of features for the new project. The problem is I wanted to dynamically change the row colour based on the contents of a cell.

The sheet is set up with validation for the status column and as you change the status the entire row will change colour to help make things more organised.

To set this up in your Google Doc click on Tools > Script Editor and paste in the below making sure you change the status options, Sheet Name, Column Length and Column Choice to match your sheet.

 1function onEdit(e) {
 2  if (e) { 
 3    var as = e.source.getActiveSheet();
 4    var ar = e.source.getActiveRange();
 5    var sheetName = "Features"; // Change this to the name of your sheet
 6    var colLength = 5; // How many columns are in the selection
 7    var colChoice = 5; // In your sheet what is the column number that contains the choice
 8 
 9    if (ar.getRow() != 1 && as.getName() == sheetName) {
10 
11      status = as.getRange(ar.getRow(), colChoice).getValue();
12 
13      rowRange = as.getRange(ar.getRow(),1,1,colLength);
14 
15      // The status what we are basing the colours on
16      if (status == 'In Progress') {
17        rowRange.setBackground("#ffa500");
18      } else if (status == 'Complete'){
19        rowRange.setBackground("#66CD00");
20      } else if (status == 'Rejected') {
21        rowRange.setBackground("#FF0000");
22        // Default and Planned Status
23      } else if (status == '' || status == 'Planned') { 
24        rowRange.setBackground("#FFFFFF");
25      }   
26    }
27  }
28}