Help with calendar

First of all, I made your calendar work as you like.
You can change the year by clicking on the Up/Down arrows in cell A1. This changes the values of cell E11 and I3 adn all the calendar cells as well. Or you can write directly in cell E11. Note that this is the only place you should change the year of this calendar.

It uses a named range (as DecSun1) to find the date of the first sunday in decembre. But it works for the current year only. On your month of December of the previous year, I created a new named range (DecSun2) which finds the first Sunday in december of the previous year. Now, your calendar is working as you like.
Next, I'll look at your coloring thing. It will require a macro. Is tis OK with you and your company?

There is something strange about the calendar in that the dates disappear from parts of the calendar when the scroll is used.

I have made a simple calendar where you enter the day month and year and it populates the month. It isn't sophisticated but it does work.

Hi Kris,
look at the attached modified workbook.
I made some changes so it should work as required.
You'll see light yellow cells for holidays' dates. Those are the cells the macro will look for dates to color the calendar. It must be a valid date in those cells. Nothing else.
I created 2 buttons. One to color the holidays' dates and the other one to revert the coloring before you change any dates. You may always change the color manually though.
Hope this helps.

Here it is working now.
I just learned that once the search parameter is set to look in VALUES, it continues with this parameters as long as it is not changed. In the macro, I did not specified to look in Values so when you opened the workbook, it was kind of reset to look in FORMULAS (default value) instead of VALUES. That's why it was not working anymore. I modified the macro so you won't have this problem again.

About the NAMED RANGE now. They are defined by the user. Look in the menu FORMULAS and then NAME MANAGER. If you do this while in your calendar workbook, you'll see lots of names with, at the bottom of the window, the REFER TO: section. There you can specify a specific cell, a range of cells or a formula that will result either in a value or a range of cells.
You can easily define a Named range by entering a name in the cell address section at the left of the formula bar at the top of the Excel window. Click like in cell E2 and then click in that section where you see E2 and type any name you want. That cell can now be referenced by its name instead of by its address. This can be very usefull when you have big formulas. It is easier to understand a formula when it says =Total_Price * Percent_Discount instead of = E2* C5 or whatever.
It is a very powerfull side of Excel.

Hope it works great for you now.
Regards

you need to go to the Visual Basic Editor. You can this by clicking ALT + F11.
In VBE, you double click on the module name and you'll see the macros.
Regards

I did not create any link, they are all from you. If you don't need this one, just click on "Break link" and you should be good.

Take a look at this
The only thing I did not do is the red coloring.
I dont know which cells you want colored

I like Popipipo solution as it does not involve MACROS.
For the RED cells, just duplicate the conditional formatting but enter only dates in column A.

Kris, I don't know what to say about the link. I did not create it myself and it should not affect the buttons either. I'm lost. Can you attached your file?

The buttons are inactive because ther are no macro's

Try to avoid merged cells, this creates problems!
Take a look at this updated file

Kris, in Excel 2007 and up, there are usually 2 types of Excel files.
*.xlsx which do not support macros
*.xlsm which DO support macros.
When you save an xlsm file to an xlsx type, it drops the macro.

There is a named range that has the name AprSun1 for both the destination worksheet and the range that you are copying onto the destination worksheet. These named ranges may or may not have the same definition in the formula bar for named ranges. Rename the named range on the range being copied to something that doesn't exist elsewhere and then copy to the destination worksheet. If the name change in the copied range has affected some formulae, make the changes necessary to regain functionality.

You are probably defining names using the Formula Tab, Define Name and then naming a range with the scope being the worksheet that you are on. This name is then being used in a formula. When you copy and paste this named range along with formulae that use the name, you will get the message.

If you use Workbook as the scope for names, you will not be permitted to use the same name for different ranges, even on other worksheets. The problem is therefore eliminated before it gets started.

here is your workbook with the offending named range deleted.
The only sheet that needs named range is the '2015 Calendar' sheet and the 'year' named range for the YEAR drop down menu.
I can't understand how the others were created at the first place but you should have no other ones.
You can see those if you click on the FORMULAS ribbon menu then on NAME MANAGER menu. In the appearing window, you should see the 12 named range used by sheet 2015 Calendar. They are all named like AprSun1 or JulSun1 meaning the first day of the month. It has a formula giving the first day of the month.
The only way I can see for tis to occur is to have copied the 2015 Calendar sheet to create a new sheet for your by weekly time sheet. By doing this, you create a copy of the named range in the new sheet and also their duplicates.

With the new sheet, you should be OK.
Regards