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
    Apr 23 '19 at 10:58
  • edited original post to include link to shared copy of sheet. Apr 23 '19 at 12:17
  • what range you want to color ? B3:NC or B:NC ?
    – user0
    Apr 23 '19 at 12:19
  • B3:NC please Apr 23 '19 at 12:30
  • you need indirect if you want to reference other sheets in conditional formatting
    – user0
    Apr 23 '19 at 12:34

=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, privacy policy and cookie policy

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