# Google-sheets – Conditional Formatting across a range of cells with a formula in Google Sheets

I have a calendar put into Google Sheets with the data put in as dates such as "9/24/2018".

I have a separate sheet with a data set that has rows that classes end such as "9/24/2018".

I want to conditionally format the entire calendar so that if any cell in the calendar has a date that matches one in the data set it will format.

I'm using this formula so far, but no dice. Not sure what I'm doing wrong.

``````COUNTIF(indirect("Training Classes!\$E:\$E"),A1)>1
``````

The calendar range is B6:X37 just in case that matters.

For the record, the formula does work if I assign it to a specific cell and put it in a cell.

So if I put somewhere `=COUNTIF(indirect("Training Classes!\$E:\$E"),S27)>1)` then it is true. Its just the conditional that's having issues.

First of all, sharing your sheet or at least a copy/example is going to get you a much better answer more quickly.

I attempted to recreate your spreadsheet as best I could given your description (completely guessing on the calendar sheet).

Based on my example, all the cells in `Calendar!B6:X37` contain a unique date. Each cell takes its date, and searches for it in `'Training Classes'!E:E`. If it finds a match, then that cell gets highlighted yellow.

The formula I use for the conditional formatting is:

``````=ARRAYFORMULA(COUNTIF(INDIRECT("Training Classes!E:E"),B6:X37))
``````

The formula you were using:

``````COUNTIF(indirect("Training Classes!\$E:\$E"),A1)>1
``````

I believe this wasn't working because: You said the calendar was in `B6:X37`, yet you reference `A1` (your working formula example uses `S27`). Also, you are missing an `=` at the start of the formula, and the `>1` appears to be unnecessary.