2

I could use some help with conditional formatting.

I would like to format the first 6 cells in a row in a Google Docs file based on the following condition: If column A is not empty and column C is, change the background of the cells in A-F to red.

How can I do this?

2

In your spreadsheet, go to tools, click script editor, and create a blank script. Replace any text inside the script with this function.

function paintRed() {

  // Gets the entire workbook
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  // Gets the sheet you are concerned with
  // Change Sheet1 to the name of your sheet
  var sheet = ss.getSheetByName("Sheet1");

  // First 6 cells of the first row
  // Change to whatever you like
  var rangeToPaint = sheet.getRange("A1:F1"); 

  // Gets the number of the last row in the sheet 
  // that has anything in it
  var lastRow = ss.getLastRow();

  // If the sheet is blank, set the first 6 cells
  // in the first row to white and end the function
  if (lastRow < 1)
  {
    rangeToPaint.setBackground("white");
    return;
  }

  // Gets column A and column C
  var rangeColumnOne = sheet.getRange("A1:A"+lastRow.toString());
  var rangeColumnTwo = sheet.getRange("C1:C"+lastRow.toString());

  // Gets the length of column A and C
  var column1Length = rangeColumnOne.getValues().toString().replace(/,/g,"").length;
  var column2Length = rangeColumnTwo.getValues().toString().replace(/,/g,"").length;

  // If column A is blank or column C is not blank,
  // set the first 6 cells to white and stop the
  // function
  if ( column1Length < 1 || column2Length > 1 )
  {
    rangeToPaint.setBackground("white");
    return;
  }

  // Otherwise, paint the first 6 cells red
  rangeToPaint.setBackground("red");

}

You will need to change Sheet1 to whatever the name of your sheet is. Then, while still in the script editor, click resources, 'current project triggers.'

Add a new trigger with 'Run' set to paintRed and Events set to 'From spreadsheet' 'On edit.'

You should be good to go.

Link to spreadsheet you can copy to your drive, then play with - https://docs.google.com/spreadsheet/ccc?key=0Ar6b2EOADTG7dGJOM0lEalhjd3pNOHNRNnBDOV8xRnc&usp=sharing

  • Why so complex? conditional formats are simple boolean validations – Manuel Ramirez Nov 14 at 14:41
  • Yeah, this is really complex. I think I was really excited about the javascript capability of google sheets back then. I'll leave it up in case anyone's specifically looking for a scripting way to do it, but I'd highly recommend just using pnuts answer – Jared Beach Nov 14 at 16:20
1

In New Google Sheets please try:

=and(not(isblank($A1)),isblank($C1))  

in Custom formula is, Backround red and Range: A1:F1.

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.