# Conditional format based on named range dates

#### Apple08

##### Active Member
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.

Last edited:

### Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

#### etaf

##### Well-known Member
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

##### Active Member
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

##### Well-known Member
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

Replies
7
Views
174
Replies
0
Views
179
Replies
6
Views
241
Replies
4
Views
226
Replies
8
Views
414