Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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
add comment

2 Answers

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
add comment

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
add comment

Your Answer

 
discard

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.