Conditionally Change the Color of Entire Row

Question:

( by Paula Lafferty )

I have found that conditional formatting multiple cells based on the value in one cell is possible in google spreadsheets via a script. I am not very familiar with writing or customizing my own scripts, but have used some from the Scripts Gallery.

My workbook has multiple sheets and I would like all sheets to run this script.

The data starts on row 3 to the end of the document (the script doesn't need to apply to rows 1&2)

If there is anything in cell K3 (or K4, K5, etc) I would like that entire row to change the background color to white.

If there is anything in cell P3 (etc.) the row should change background color to green

If there is anything in cell Q3 (etc.) the row should change background color to yellow.

I would like them to work in that order. So if there is something in both K3 and P3 the row should be Green, if there is something in all three cells the row should be Yellow.

I've found a few scripts online, but I have not been able to edit them to do what I want. Is this possible with scripts? Any help would be appreciated!

Solution:

Have a look at the following screenshot of my Spreadsheet:

Have a look at the following script code:

///////////////////////////////////////

function onEdit(e) {
  changeColor();
};

function changeColor() {
  var activeSheet = SpreadsheetApp.getActiveSheet();
  var numRows = activeSheet.getMaxRows();
  var numColumns = activeSheet.getMaxColumns();
  var dataRange = activeSheet.getRange(1, 1, numRows, numColumns);
  var values = dataRange.getValues();
  var backGroundColors = dataRange.getBackgrounds();
  for(var i=2;i<values.length;i++) {
    if(values[i][16] != "") // Column Q
      for(var j=0;j<numColumns;j++)
        backGroundColors[i][j] = "#ffff00"; // Yellow
    else if(values[i][15] != "") // Column P
      for(var j=0;j<numColumns;j++)
        backGroundColors[i][j] = "#00ff00"; // Green
    else if(values[i][10] != "") // Column K
      for(var j=0;j<numColumns;j++)
        backGroundColors[i][j] = "#ffffff"; // White
  }
  dataRange.setBackgrounds(backGroundColors);

};

///////////////////////////////////////

put the above code in the Script editor of your Spreadsheet.

And If you are not much familiar with scripts then check out the following link:
http://igoogledrive.blogspot.com/2012/08/how-to-write-script-in-google.html 

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private and confidential projects:

http://igoogledrive.blogspot.com/p/paid-projects.html

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

Kishan.

iGoogleDrive