So, I have a Gantt chart style diary. I want to apply conditional formatting to any column whose date (in row 2), is a public holiday. There is a sheet called 'Public Holidays', that has the dates of all public holidays in column A.

I found a relevant formula to try from Peter Smulders answer to this topic: How to check if value is in range of cells?

Here's my version:

=NOT(ISERROR(MATCH(B$2:RV$2, 'Public Holidays'!$A:$A,0)))

Nothing happens. It doesn't activate on any cells or columns whether public holidays or not. Weirdly though, if I paste that formula into a bunch of cells, it returns correctly, ie any cell in a column whose date in row 2 is a public holiday returns TRUE and any cell in a column whose date is not a public holiday returns FALSE.

I don't understand why it works as a cell formula but not as a conditional formatting formula.

Any help would be greatly appreciated.

Here's a copy of my sheet: https://docs.google.com/spreadsheets/d/1sYI2oDyDR5p5oIJvE_AKIUm_PMReL4nGfXlMU5YvRxI/edit?usp=sharing

  • share a copy of your sheet pls
    – user0
    Commented Apr 23, 2019 at 10:58
  • edited original post to include link to shared copy of sheet. Commented Apr 23, 2019 at 12:17
  • what range you want to color ? B3:NC or B:NC ?
    – user0
    Commented Apr 23, 2019 at 12:19
  • B3:NC please Commented Apr 23, 2019 at 12:30
  • you need indirect if you want to reference other sheets in conditional formatting
    – user0
    Commented Apr 23, 2019 at 12:34

1 Answer 1


=NOT(ISERROR(MATCH($B$2:$NC$2, INDIRECT("'Public Holidays'!$A:$A"), 0)))



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.