Google Sheets – Check Cells for Specific Date Range

google sheets

I am trying to check if a number of cells, containing multiple rows and cols, contains dates from another range (in this case days off). If the dates match I'd like to color the cell red using a conditional comment.

      | Col A      | Col B      | Col ?
    ----------------------------------------
    1 | Dates 1    | Dates 2    | Days off 
    ----------------------------------------    
    2 | ma 20 feb. | ma 6 mrt.  | wo 22 feb.
    3 | wo 22 feb. | wo 8 mrt.  | do 2 mrt.
    4 | do 23 feb. | do 9 mrt.  | do 9 mrt.
    5 | di 28 feb. | di 14 mrt. | do 16 mrt.
    6 | do 2 mrt.  | do 16 mrt. | 
    7 | vr 3 mrt.  | vr 17 mrt. |

So in the example above cell A3 matches an off day and should be coloured red. Same goes for cell A6, cell B4 and cell B6.

Any help would be much appreciated.

Best Answer

Assuming Column ? is ColumnF, please select A:B and in Conditional formatting apply a Custom formula is of:

=match(A1,$F:$F,0)

and select red.

MATCH

The above is case insensitive but you may need to be careful with spaces.

Related Solutions

Advanced Date Logic in Google Sheets

Yes.

Use Conditional Formatting with three rules: (Format -> Conditional formatting)

"Date is before" "in the past week" -> red
"Date is after" in the past week" -> green
"Date is" "in the past week" -> orange

This will colour all dates more than a week away in green, all dates coming in the next week orange and the remainder of the dates in red. Empty cells will be left alone.

Google-sheets – Conditional formatting based on cell values in same column

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.

Best Answer

Related Solutions

Advanced Date Logic in Google Sheets

Google-sheets – Conditional formatting based on cell values in same column

Related Topic