Conditional formatting based on cell values in same column

Question

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.

Answer 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: [email protected]
**/

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.

Answer 2

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