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

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

1 Answer 1

Reset to default
0 

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

0

Improve this answer
0

Your Answer

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

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