I'm building a calendar document, which has a grid on the left with calendar days (columns A:G), and then a list on the right hand side with details as to what's happening on those days (starting in I). When an item in the list corresponds to a day on the left, I want to colour that day. It's based on a very slightly modified version of this template: https://docs.google.com/spreadsheets/d/1mbxsHjsodBng9tO4_6LaJ73k6Bm-G45DGB_Yc3rWrmo/edit#gid=0

I've got as far as applying =COUNTIF(I2:I,A3)>0 as a custom formula to A3, which correctly colours A3 if there is anything in column I that matches the content of A3. What I would like to do is apply that to the entire range A3:G7 based on whichever value is present in each individual cells. It feels like there should be a way to do this without having to set up this formula 365 times across my sheet, but everything that gets close doesn't seem to apply across the range like I expect.

Is there something I'm missing?

1 Answer 1


In order to be able to account of the month is being considered I stablished that every 9 rows there would be a new month. Then you can set this formula in range A1:G:


enter image description here Here you have the link to my spreadsheet copy: https://docs.google.com/spreadsheets/d/1IN_OCfd3lDQlniN_UTsO3puGWiWFuFejFxiTvUYrwq8/edit?usp=sharing

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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