My previous post Excel calendar (vba) used vba and formulas to extract events.

This post demonstrates how to filter a table using a calendar. Select a date on the calendar and events on that specific day are automatically filtered.

I would certainly have liked the calendar above the table but that makes it impossible to adjust the calendar's cell widths to the same size. If I put the calendar next to the table, it disappears when filtering the table.

Instructions:

Create calendar

See steps in my previous post.

Conditional formatting

For some reason I don´t understand, a table name is not allowed in a conditional formatting formula . I ended up creating a named range:

Conditional formatting formula:

See remaining steps in my previous post.

Vba code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("E4:K9")) Is Nothing And Target.Cells.Count = 1 Then
ActiveSheet.ListObjects("Table1").Range.AutoFilter _
Field:=1, Criteria1:=">=" & Target.Value, Operator:=xlAnd, Criteria2:="<" & Target.Value + 1
SortTable
ElseIf Not Intersect(Target, Range("G11")) Is Nothing Then
ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1
SortTable
End If
End Sub
Sub SortTable()
With ActiveWorkbook.Worksheets("Calendar").ListObjects("Table1").Sort
.SortFields.Clear
.SortFields.Add Key:=Range("Table1[[#All],[Start]]"), SortOn:=xlSortOnValues, Order:= _
xlAscending, DataOption:=xlSortNormal
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
  1. Right click on sheet
  2. Click "View code"
  3. Paste code to sheet module
  4. Return to excel

How to add new records to the table

  1. Select the last cell in the table
  2. Press Tab key
  3. Enter new values

New records are also highlighted in the calendar. Make sure you have selected the correct month to be able to see them.

Recommended blog posts

Filter a table using the selection change event

Extract unique distinct values from a filtered table (udf and array formula)

Quickly filter a column in an excel table

Download excel *.xlsm file

Table and calendar.xlsm