Web Applications Stack Exchange is a question and answer site for power users of web applications. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
Perhaps a helper column can give you the solution you're looking for, without the use of a script. See this answer I gave: webapps.stackexchange.com/a/41448/29140 – Jacob Jan Tuinstra Apr 26 '13 at 13:52
Use this formula to return true or false: =ARRAYFORMULA(FILTER(A:A;A:A<>"")=FILTER(C:C;C:C<>"")) – Jacob Jan Tuinstra Apr 26 '13 at 14:04

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.

share|improve this answer
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

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

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.