Conditional formatting using other cells in Google Spreadsheet

You're on track. The error you're getting with .getRange() is probably due to invalid values for rows or columns. To avoid this sort of problem, you could instead read the entire spreadsheet's contents into an array, relying on .getDataRange().getValues(). Once that's done, you can access values for any column within the resulting array.

Some other comments:

• Spreadsheet rows & columns start numbering at 1, while Javascript arrays start at 0. This is always tricky, so it's best to avoid using the numbers in your code as much as possible. Instead, use variables. In this example, the array indices are calculated by subtracting 1 from the spreadsheet column numbers.
• When read from a spreadsheet using getValue() or getValues(), cells that contain dates appear as javascript date objects, e.g. "1/1/2013 16:54:00" is (new Date(1357077240000)), if you check the debugger. Therefore, you don't need to mess around with new Date(...) in your code.
• The AND operator in Javascript is &&. (OR is ||.)
• setBackgroundColor() has been deprecated, use setBackground(color) instead. If you have a range of colors to set (as you do), your script will be more efficient by using setBackgrounds() just once, which accepts a two-dimensional array of colors.
• Another efficiency improvement can be made by re-arranging your various if statements. (The idea is to reduce the computational investment. In the case of slow operations like many of the Google API calls, this thinking can save your script from timeouts. In this example, the .getTime() function is quick, so it's not really necessary to refactor the code.)

Updated code

function formatting() { var now = new Date().getTime(); var twoDaysInMilliseconds = 2 * 24 * 60 * 60 * 1000; //172800000; var headers = 1; // # rows of headers to skip var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Enquiry Tracking'); var data = sheet.getDataRange().getValues().splice(headers); // all data, skipping headers var colBooked = 5-1; // Column numbers -1 to map to 0-based array var colDate = 1-1; var colFU = 6-1; var colors = []; for (var row = 0; row < data.length; row++) { colors[row] = ['transparent']; // default - no color if (data[row][colBooked] === 'y') { colors[row][0] = 'LawnGreen'; } else { if (data[row][colFU] < 1) { var rowdate = (data[row][colDate]).getTime(); if (now - rowdate > twoDaysInMilliseconds) { colors[row][0] = 'Tomato'; } } } } // Set the colors for all data cells in ColFU sheet.getRange(1+headers,colFU+1,colors.length,colors[0].length).setBackgrounds(colors); }