Conditional format based on named range dates

Apple08

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

Some videos you may like

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
Joined
Oct 24, 2012
Messages
3,954
Office Version
  1. 365
Platform
  1. MacOS
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
Joined
Nov 1, 2014
Messages
353
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
Joined
Oct 24, 2012
Messages
3,954
Office Version
  1. 365
Platform
  1. MacOS
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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,549
Messages
5,529,467
Members
409,883
Latest member
asharris90
Top