This calendar lets you schedule events on the data sheet. Select a cell (calendar date) and events on that day are shown below the calendar.
Today´s date is highlighted yellow. Days with one or more events are also highighted.
Table of Contents
Type Year: in cell B2, Month: in cell E2 and so on...
Change font color for dates not in selected month
Highlight days with events
Dynamic named range
How to create an array formula
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("B5:H10")) Is Nothing Then
Range("G2") = Target.Value
Hide value in cell G2
INDEX(array, row_num, [column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range.
MATCH(lookup_value, lookup_array, [match_type])
Returns the relative position of an item in an array that matches a specified value in a specific order
Returns the k-th smallest value in a data set.
Returns the row number of a reference.
Returns value_if_error if expression is an error and the value of expression itself otherwise.
Visualize date ranges in a calendar part 2
Monthly calendar template
Calendar with scheduling [vba]
Heat map calendar
Calendar – monthly view
Yet another excel calendar
Run a Macro from a Drop Down list [VBA]
Show and hide a picture [VBA]
Find numbers closest to sum
Save invoice data [VBA]
Open Excel files in a folder [VBA]
Split data across multiple sheets [VBA]
In the "Calculating dates (formula)" section, Step #2... you misspelled "January" as "Janauary" in the latter part of the formula.
[...] number formats, and much more. Home > Excel > Automate > Use a calendar to filter a table← Previous post - Use a calendar to filter a tableFiled in Automate, Drop down lists, Excel, Search/Lookup on Sep.10, [...]
[...] How to create an Excel calendar with VBA [Get Digital Help] [...]
hi,i am new to this blog.
but i hope i can get some help regarding a pop up calendar.
i found a pop up calendar for excel 2007,but when i try to use it in Excel 2010, the calendar does not appear. and i think it is related with the format behind.can you help me?thank you for any help and sorry for my poor English.
Do you have a link to the pop up calendar?
Hi, I would like to use this example with my data set however I'd like to visually show the amount of events per date to understand when are we the busiest, slowest, etc. and be able to forecast using this data. Ideally I would like some sort of data bar or color change indicating the level for each date (Jan 1 has 10 items while Jan 2 has 3 and I can visually see that in each cell instead of seeing numbers or a solid color for each cell (here yellow and blue).
Also, would it be possible to have Excel know the days of the month as in first 7 days of the month, last day of the month, every Monday of the month. I'd eventually like to get formulas set up to tell me on average how busy we are during these periods.
Here is a basic example. It looks like a heat map.
Highlight dates VBA
Private Sub Worksheet_Change(ByVal Target As Range)
Set Rng = Range("B5:H10")
If Target.Address = "$C$2" Or Target.Address = "$E$2" Then
'Remove previous formatting
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
For Each Cell In Rng
For Each Value In Worksheets("Data").Range("Data1")
If Int(Value) = Cell Then a = a + 1
If a > 0 Then
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent4
.TintAndShade = 1 - (a / 4)
.PatternTintAndShade = 0
'Reset counter a
a = 0
Download excel *.xlsm file
Contact me if you want more features.
[...] David asks: [...]
Hi there, I have downloaded the above calendar - and want to add another filter so I can categorize the events/meetings in the calendar, e.g. Reg for regulatory, Ben for benefits - and so when you click on these it will filter for that type - is this possible to do? Appreciate your help with this.
Yes, it is possible. Upload a file and I´ll see what I can do.
This looks great. I would like to use your calendar for a spreadsheet I am currently using. How could I just change where the event section refers to so it will automatically fill in the dates and times I need.
To give you context, I have a spreadsheet with many columns containing information. In these columns I have multiple dates/times for some rows. I want to create a calendar that references my spreadsheet with its events. I would like to keep all the information so that I can look at it still in the detailed view of each day (with all the information from the original row and also be able to reference the spreadsheet for multiple times for the same row but on different days).
How would I change the where the events are referenced to?
How could I add another option for dates so that I can add an extra column of values (I have the original dates column in the data sheet linked to another workbook?
[…] I got this codes from: https://www.get-digital-help.com/2012/09/05/excel-calendar-vba/ […]
Please I would like to know how to highlight 2nd and fourth Saturday and all sunday as weekend. Can you please suggest vba code or formula.
[…] How to create an Excel calendar with VBA [Get Digital Help] […]
Thank you so much for the helpful template. This is awesome! However, when I choose January, it gives me all N/A values, can you please let me know what's going on? Thanks
I love this template. I am trying to find a way to be able to show student schedules, and I think I could use this but I would have to alter it and I'm not completely sure what I am doing. I have students that come to me for a 3 week rotation. I want a monthly calendar that will highlight the time each student is with me and then also allows me to input schedules for each day. Example student one is with me for 7/3 to 7/23, then on 7/3 they have orientation, on 7/5 they have conference and department meeting. Etc.
Have you seen this calendar?
I think it will suit your requirements better.
Hi, your calendar is great for me. I want to add a new column D in the Data sheet and show the details in the calendar sheet (I added a new column right after the "Text:", how can I revise the array formula to show it properly?
How to add a formula to your comment:
How to add VBA code to your comment:
[vb 1="vbnet" language=","]
How to add a picture to your comment:
Upload picture to postimage.org
Add picture link to comment.
You can contact me through this webpage
Mail (will not be published) (required)
Notify me of followup comments via e-mail
User Defined Functions
Posts in category