Google-sheets – Setting values with if and then & numbers across sheets

You could always create the column in one sheet, and copy-paste it to the other sheets, one sheet at the time. But I guess that's what you're trying to avoid.

It is possible to do this with a Google Apps Script. The following script adds a menu in a spreadsheet, which allows you to insert or remove a column on all sheets.

function addColumnOnAllSheets() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var colIdxStr = Browser.inputBox("Insert column right of column no");
  if (colIdxStr == 'cancel') {
    return;
  }
  var colIdx = parseInt(colIdxStr);
  if (colIdx == NaN) {
    Browser.msgBox("You must enter a number or 'cancel'");
    return;
  }
  var sheets = spreadsheet.getSheets();
  for (var i = 0; i < sheets.length; i++) {
    var sheet = sheets[i];
    sheet.insertColumnAfter(colIdx);
  }
}

function removeColumnOnAllSheets() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var colIdxStr = Browser.inputBox("Remove column no");
  if (colIdxStr == 'cancel') {
    return;
  }
  var colIdx = parseInt(colIdxStr);
  if (colIdx == NaN) {
    Browser.msgBox("You must enter a number or 'cancel'");
    return;
  }
  var sheets = spreadsheet.getSheets();
  for (var i = 0; i < sheets.length; i++) {
    var sheet = sheets[i];
    sheet.deleteColumn(colIdx);
  }
}

function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [
    {name: "Add column on all sheets", functionName: "addColumnOnAllSheets"}, 
    {name: 'Remove column on all sheets', functionName: 'removeColumnOnAllSheets'}
  ];
  sheet.addMenu("Columns", entries);
}

To install this script in your own spreadsheet, click Tools -> Script editor, and paste the code into the script editor. Save the script, and reload the spreadsheet in order to show the menu.

I have set up an example spreadsheet to demonstrate the script.

There might be a better way, but try three conditional format rules on C1 in this order:

1. "Custom formula is"; Value: =IF(A1="Done",TRUE,FALSE): set background to White

2. "Date is after"; "exact date..."; Value: =NOW()+3: set background to Green

3. "Cell is not empty": set background to Red. This is the default if the cell is not "Done" and the date is not after three days from now.

You could also keep rule 1, and then have rule 2 being a Colour Scale. You might need to put an extra cell to convert NOW() into a day count, though: =NOW()-3 for example.