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.

### Drop down lists

#### Select year

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

#### Select month

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

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. Click "Conditional formatting" button
4. Click "New Rule..."
5. Click "Use a formula to determine which cells to format"
6. Format values where this formula is true:
=B5=TODAY()
7. Click "Format..." button
8. Go to "Fill" tab
9. Pick a color
10. Click OK
11. Click OK

#### Change font color for dates not in selected month

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

#### Highlight days with events

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

### Formulas

#### Dynamic named range

1. Select sheet "Data"
2. Go to tab "Formulas"
3. Click "Name Manager" button
4. Click "New..."
5. Name: Data
6. Refers to:
=OFFSET(Data!\$A\$2,,,COUNTA(Data!\$A\$1:\$A\$1000)-1,3)
7. Click OK!
8. Click 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. Click in formula bar
3. Paste array formula
4. Press and hold Ctrl + Shift
5. Press Enter

### Visual basic for applications

1. Right click on Calendar sheet
2. Click "View code"
3. Copy vba code below.
```'Event code that is executed 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. Click OK!