Author: Oscar Cronquist Article last updated on October 03, 2019

This workbook contains two worksheets, one worksheet shows a calendar and the other worksheet is used to store events. The calendar sheet allows you to enter a year and use a drop down list to select a month.

A small VBA event code tracks which cell you have selected and shows the corresponding events accordingly.

How this workbook works

The animated image above shows how to enter data and how to select a given date. Today's date is highlighted yellow, days with one or more events are also highlighted, in this example blue.

Excel extracts data dynamically meaning the named range grows automatically when new data is entered, we don't need to change the formula cell references.

How I created the calendar

Table of Contents

  1. Calendar
    1. Drop down lists
    2. Headers
    3. Calculating dates (formula)
  2. Conditional formatting
    1. Today
    2. Dates not in selected month
    3. Events
  3. Formulas
    1. Dynamic named range
    2. Calendar sheet
    3. How to create an array formula
  4. Visual basic for applications
  5. Get excel *.xlsm file

Drop down lists

Select year

  1. Select cell B2
  2. Go to tab "Data"
  3. Press with left mouse button on "Data Validation" button
  4. Allow: List
  5. Source:2012,2013,2014,2015,2016
  6. Press with left mouse button on OK

Select month

  1. Select cell E4
  2. Go to tab "Data"
  3. Press with left mouse button on "Data Validation" button
  4. Allow: List
  5. Source:January, February, March, April, May, June, July, August, September, October, November, December
  6. Press with left mouse button on OK

Headers

Type Year: in cell B2, Month: in cell E2 and so on...

Calculating dates (formula)

  1. Select cell B5
  2. Formula:
    =DATE($C$2,MATCH($E$2,{"January"; "February"; "March"; "April"; "May"; "June"; "July"; "August"; "September"; "October"; "November"; "December"},0),1)-WEEKDAY(DATE($C$2,MATCH($E$2,{"January"; "February"; "March"; "April"; "May"; "June"; "July"; "August"; "September"; "October"; "November"; "December"},0),1),2)+1
  3. Select cell B6
  4. Formula:
    =B5+1
  5. Copy cell B6 (Ctrl + c)
  6. Select cell range D5:H5
  7. Paste  (Ctrl + v)
  8. Select cell B6
  9. Formula:
    =B5+7
  10. Copy cell range C5:H5
  11. Select cell range C6:H6
  12. Paste (Ctrl + v)
  13. Copy cell range B6:H6 (Ctrl + c)
  14. Select cell range B7:H10
  15. Paste (Ctrl + v)

Conditional formatting


Highlight Today

  1.  Select cell range B5:H10
  2. Go to tab "Home"
  3. Press with left mouse button on "Conditional formatting" button
  4. Press with left mouse button on "New Rule..."
  5. Press with left mouse button on "Use a formula to determine which cells to format"
  6. Format values where this formula is true:
    =B5=TODAY()
  7. Press with left mouse button on "Format..." button
  8. Go to "Fill" tab
  9. Pick a color
  10. Press with left mouse button on OK
  11. Press with left mouse button on OK


Change font color for dates not in selected month

  1. Repeat 1-5 steps above
  2. =MONTH(B5)<>MONTH($B$6)
  3. Press with left mouse button on "Format..." button
  4. Go to "Font" tab
  5. Pick a color
  6. Press with left mouse button on OK
  7. Press with left mouse button on OK


Highlight days with events

  1. Repeat 1-5 steps above
  2. =OR(B5=INT(INDEX(Data,0,1)))
  3. Press with left mouse button on "Format..." button
  4. Go to "Fill" tab
  5. Pick a color
  6. Press with left mouse button on OK
  7. Press with left mouse button on OK

Formulas


Dynamic named range

  1. Select sheet "Data"
  2. Go to tab "Formulas"
  3. Press with left mouse button on "Name Manager" button
  4. Press with left mouse button on "New..."
  5. Name: Data
  6. Refers to:
    =OFFSET(Data!$A$2,,,COUNTA(Data!$A$1:$A$1000)-1,3)
  7. Press with left mouse button on OK!
  8. Press with left mouse button on Close


Event formulas

  1. Select sheet "Calendar"
  2. Select cell B13
  3. Array formula:
    =IFERROR(INT(INDEX(Data, SMALL(IF(INT(INDEX(Data, 0, 1))=$G$2, MATCH(ROW(Data), ROW(Data)), ""), ROW(A1)), COLUMN(A1))), "")
  4. Enter this formula as an array formula, see these steps if you don't know how.
  5. Select cell D13
  6. Array formula:
    =IFERROR(INDEX(Data, SMALL(IF(INT(INDEX(Data, 0, 1))=$G$2, MATCH(ROW(Data), ROW(Data)), ""), ROW(B1)), COLUMN(A1)), "")
  7. Select cell F13
  8. Array formula:
    =IFERROR(INDEX(Data, SMALL(IF(INT(INDEX(Data, 0, 1))=$G$2, MATCH(ROW(Data), ROW(Data)), ""), ROW(C1)), COLUMN(B1)), "")
  9. Select cell H13
  10. Array formula:
    =IFERROR(INDEX(Data, SMALL(IF(INT(INDEX(Data, 0, 1))=$G$2, MATCH(ROW(Data), ROW(Data)), ""), ROW(D1)), COLUMN(C1)), "")


How to create an array formula

  1. Select a cell
  2. Press with left mouse button on in formula bar
  3. Paste array formula
  4. Press and hold Ctrl + Shift
  5. Press Enter

Visual basic for applications

  1. Press with right mouse button on on Calendar sheet
  2. Press with left mouse button on "View code"
  3. Copy vba code below. 
    'Event code that is rund every time a cell is selected in worksheet Calendar
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'Check if selected cell address is in cell range B5:H10
If Not Intersect(Target, Range("B5:H10")) Is Nothing Then

'Save date to cell G2
Range("G2") = Target.Value
End If

End Sub
  4. Paste to worksheet module.
Note, save your workbook with file extension *.xlsm in order to attach the code to the workbook.

Hide value in cell G2

  1. Select cell G2
  2. Press Ctrl + 1
  3. Go to tab "Number"
  4. Select category: Custom
  5. Type ;;;
  6. Press with left mouse button on OK!

Calendar-event-finish

Get the Excel file


Calendarv2.xlsm

Weekly Blog EMAIL

Welcome! I am Oscar and here to help you out.

Feel free to comment and ask Excel questions.

Make sure you subscribe to my newsletter so you don't miss new blog articles.

Related articles

Calendar with scheduling [vba]

Here is my contribution to all excel calendars out there. My calendar is created in Excel 2007 and uses both […]

Run a Macro from a Drop Down list [VBA]

This article demonstrates how to run a VBA macro using a Drop Down list. The Drop Down list contains two […]

Show / hide a picture using a button

This article explains how to hide a specific image in Excel using a shape as a button. If the user […]

How to create a list of comments from a worksheet programmatically

Did you know that you can select all cells containing comments in the current sheet? Press F5, press with left […]

Find the most/least consecutive repeated value [VBA]

This post Find the longest/smallest consecutive sequence of a value has a few really big array formulas. Today I would like to […]

Calendar – monthly view

This article describes how to build a calendar showing all days in a chosen month with corresponding scheduled events. What's […]