Conditional format based on named range dates

[Apple08]

Hi All

I have a named range 'Holidays' from BA2 to BA91, I need to look into the worksheet and highlight any dates that are equal to the named range 'Holidays' dates.

I have tried to use Cell Value equal to ='Holidays' or without quote =Holidays but it didn't work. Please could anyone help?

Also I wonder is there a way to highlight weekends, like Sat and Sun? The date format is Sat 09/03/2019 Thanks.

 

[etaf]

you should be able to use conditional formatting and countif()
also weekday() should do the weekends

select the range in the spreadsheet
then assuming the dates start at A2

conditional formatting rule

=Countif( holidays, A2 ) >0
that means that day was found in your holiday list and so should highlight

then
=weekday(A2,2)>5
using the , 2 counts monday as a 1 and sunday as a 7
so if > 5 must be a weekend
set that up as a second rule in conditional formatting

 

[Apple08]

Hi etaf

The first formula for named range works perfectly! Thanks for that. However the second formula for weekend highlight doesn't work probably, my dates start from D8, so I type in formula =weekday(D8,2)>5 but then a lot of cells are highlighted but they are not dates.

 

[etaf]

try putting that formula in a different column as a test and copy down from row 8 and see what you get , true/false - true for weekends