1

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

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

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.