Conditional format based on named range dates

Apple08

Active Member
Joined
Nov 1, 2014
Messages
450
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

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,136
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top