Highlight cells based on public holidays for different states

New Contributor

Greetings everyone, 

I'm trying to highlight cells in columns A:H if they fall on public holiday dates mentioned in the cells J2:L18. Now what if the public holidays differ according to different states? Ex: If we highlight the columns based on state – VIC’s public holidays, and if state NSW’s date fall on the same date but it is not a public holiday, how do we highlight cells based on different state’s holidays without disturbing other states' normal days?

Please refer to the formula I have used. 

Thanks in a







2 Replies
best response confirmed by zaidk4496 (New Contributor)

@zaidk4496 I would start by assigning a name to each range of state holidays, using the abbreviations used in column B.


Then the following rule, applied to columns A:H will highlight the row if the date in A is a holiday in state B:




Please find a working example attached.



@Riny_van_Eekelen Got it. 

You're a legend.