3

In Google Spreadsheet, how do I change a cell's background color to red if the date in the cell matches the date in another cell in the same column? Column E in my spreadsheet is a list of due dates for various projects. I'd like the cells in column E to change to red if the dates match.

I realize variations of this question have been asked many times before and that it will require an Apps Script to accomplish. I'm having a hard time understanding how to adapt the Apps Scripts that I've found to my needs.

1

Assuming the actual finishing date is in Column F and the sheet is called Sheet1, paste the code below inside your spreadsheet under Tools >> Script editor, then hit Save.

/***
* Color Code+ 
* Conditional formatting with cell referencing for Google Spreadsheets
* Support: scripts@bryanp.com
**/

function onEdit() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var s = ss.getSheetByName('Sheet1');
var values1Rule1 = s.getRange('E1:E').getValues();
var values2Rule1 = s.getRange('F1:F').getValues();
var range3Rule1 = s.getRange('E1:E');
var color1 = 'red';

for (var row in values1Rule1) {
for (var col in values1Rule1[row]) {
if (values1Rule1[row][col] == values2Rule1[row][col]) s.getRange(s.getRange('E1').offset(row, col, 1, 1).getA1Notation()).setBackgroundColor(color1);
else s.getRange(s.getRange('E1').offset(row, col, 1, 1).getA1Notation()).setBackgroundColor('white'); }}
};

I used this script generator to make creating the code easier. You can adapt it to your needs.

  • Thank you both for your help. I really hope Google addresses this glaring omission in functionality soon. likeitlikeit, I don't have a column F. Is this possible to do with only one column? – user39014 Apr 26 '13 at 15:50
1

Applicable to one or more other cells of a particular value and also to other values that repeat, in New Google Sheets please try:

Custom formula is =countif(E:E,E1)>1 with Background checked, colour red and range: E:E

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.