Conditionally format cells for holidays and paydays in a created calendar [Solved]

Conditionally format cells for holidays and paydays in a created calendar

Using Excel 2007.
I've found some formulas to generate an "automatic" calendar when you change the year.  There are actually 2 formulas:
=IF($A$2="Sun",1,"")    for the first week of the month  and  =IF(ISERROR(G5+1),"",IF(G5+1>DAY(TEXT(TEXT(IF($A$3="December",1,VLOOKUP($A$3,"January",1;"February",2;"March",3;"April",4;"May",5;"June",6;"July",7;"August",8;"September",9;"October",10;"November",11;"December",12,2,FALSE)+1)&"/1/
"&$AA$1,"mm/dd/yy")-1,"mm/dd/yy")),"",G5+1))   for the subsequent weeks of the month.
I'm trying to add to this conditional formatting for holidays and paydays.  I have a named range - Holiday - showing the holidays for each month.  My problem is in how to look up the dates in the range based on the date in the calendar using the above formuals. 
The biggest issue, I think, is that the above formuals return a value that is always ending in the year 1900 and my holiday dates are for the "correct" year (02/21/2011 for example).  I would like to potentially add up to 4 lists to this calendar to highlight
dates.
Any thoughts on how to pick the right date from the range?  Different formulas to generate the calendar / etc.?  I know there are a lot of ways to generate "automated" calendars, but I'm off creating one in a format that works for me, so I'm just trying
to find something that works.
Thanks in advance for all of your help.

Keys to the Problem Conditionally format cells for holidays and paydays in a created calendar

Download SmartPCFixer to Fix It (Free)

Hi Chad,
I received the file from you, and have amended it and sent a revised version back to you.
To anyone interested,
It's a bit too complex to record all the formulae here, but essentially there were 3 across x 4 down blocks of 7x6 cells making up the calendar, and Chad wanted public holiday dates together with dates from 2 other named ranges to be highlighted in different
colours on the appropriate day.
This is the type of CF formula I used for the block A5:G10 (i.e.
January days), which contains just the day:
=IF(A5="",FALSE,ISNUMBER(MATCH(DATE(year,1,A5),Holidays,0)))
where year is a named cell containing the data-validated chosen year.
Similar formulae were set up for the other named ranges, with different colours.
This block of CF formulae was then applied to the other blocks, with the 1 in the middle changed to suit
each month (Feb = 2, Mar =3 etc).
I also took the opportunity of revising some of the formulae used to generate the calendar itself.
Hope this helps.
Pete

Run CHKDSK

• Go to the Start button.
• In the Search box, type "cmd" (without quotes) and press Enter.
• At the command prompt, type "chkdsk /r /f" (without quotes) and press Enter.
• Restart your computer.

Recommended Method to Fix the Problem: Conditionally format cells for holidays and paydays in a created calendar:

How to Fix Conditionally format cells for holidays and paydays in a created calendar with SmartPCFixer?

1. Download SmartPCFixer. Install it on your computer.

2. After the scan is done, you can see the errors and problems which need to be fixed.

3. The Repair part is finished, the speed of your computer will be much higher than before and the errors have been removed. You can also use other functions in SmartPCFixer. Like dll downloading, junk file cleaning and print spooler error repair.

Related: Best Way to Download Satellite L645-SP4004M WinDVD BD for Toshiba v.10.0.6.110 driver,Download Toshiba Satellite C655D-S5057 Wireless LAN Driver v.8.0.0.258.0,Method to Update & Download Toshiba Satellite L650-BT2N22 Web Camera Application v.2.0.3.37 driver,How Can You Update & Download Toshiba Satellite L775D-S7220GR Value Added Package v.1.6.0130.640204 driver,Download Toshiba Satellite Pro L750-SP5176FM Sleep Utility v.1.4.0025.000101 driver,Best Way to Download NVidia GeForce 6100 VGA Driver v.304.51 Certified,How Can You Update & Download NVidia GeForce 9300/nForce 730i VGA Driver v.310.19 Certified,Method to Download NVidia GeForce GT 330M Driver v.340.65,How Can I Update & Download NVidia GeForce GTX 590 Driver v.280.26 WHQL,Method to Download NVidia Tesla C2050 Driver v.319.17,Way to Download RealTek RTL8100C(L) Driver v.5.01,Way to Download RealTek RTL8100E Drivers v.694,Way to Update & Download RealTek RTL8101L Auto Installation Program v.6.110 driver,Method to Update & Download RealTek RTL8111G PXE and RPL ROM code v.2.58 driver,How to Update & Download RealTek RTL8411B(N) Driver v.10.003,Best Way to Update & Download ASUS A53SV nVidia Graphics Driver v.8.17.12.6686,Method to Herunterladen ASUS K75VJ Intel Rapid Storage Technology Treiber v.11.6.0.1030,How Can You Update & Download ASUS CG8580 Intel Chipset Driver v.9.3.0.1019,Method to Update & Download ASUS K41VD Intel INF Update Driver v.9.1.1.1015,Way to Update & Download ASUS Pro70T NB Probe v.3.0.0026 driver
Read More: Troubleshooter of Error: Computer restarts when I put my admin password,Computer reboots after exiting forced sleep mode (powrprofmode setsuspend state) [Solved],Troubleshoot:Computer screen crashing spontaneously and Problem Event Name: Blue Screen BCCode: 116.,Fast Solution to Problem: Computer Crashes; slow to boot; Non-responsive,How to Fix Problem - Computer turns off automatically while playing Need for speed most wanted.also making a sound in speakers.?,Can't change homegroup sharing permission,Can't connect XP shared printer to Win7 except as a local printer.__,Can You Connect the LAN Adapter for Wii On your Windows 7 Laptop?,Can These Files Be Deleted,Can’t install Microsoft Flight Simulator X Gold Edition error 1722