Use a calendar to filter an Excel defined Table

This article demonstrates how to filter an Excel defined Table based on the selected cell in a calendar. The calendar highlights dates containing appointments or events, the clear button below the calendar deletes all filter conditions.

My previous post Excel calendar (vba) used a formula to extract events based on selected cell, this article shows you event code and a macro to filter the Excel defined Table itself.

How to use this workbook

The animated image below shows what happens with the Excel Table when you select different cells in the calendar, it also shows what happens when you press with left mouse button on the "clear filter" cell.

The Excel defined table and calendar, unfortunately, have to be located like the image shows above. The Excel Table will filter out calendar rows if they are next to each other.

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.

How I created this workbook

The calendar is dynamic meaning it changes automatically based on the selected year and month.

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:

Update: You can use the INDIRECT function in order to use Excel defined Tables in Conditional Formatting formulas, you can read more about it here: How to use a Table name in Data Validation Lists and Conditional Formatting formulas

Conditional formatting formula:

Conditional Formatting formula for highlighting days containing events:

See remaining steps in my previous post.

Vba code

'Event code that is triggered when the user selects any cell in cell range E4:K9
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'Check if selected cell is in cell range E4:K9 and the number of selected cells are 1
If Not Intersect(Target, Range("E4:K9")) Is Nothing And Target.Cells.Count = 1 Then

'Apply a filter to first column in Excel table named Table1 based on selected cell's date
ActiveSheet.ListObjects("Table1").Range.AutoFilter _
Field:=1, Criteria1:=">=" & Target.Value, Operator:=xlAnd, Criteria2:="<" & Target.Value + 1
SortTable

'Clear filter if cell G11 is selected
ElseIf Not Intersect(Target, Range("G11")) Is Nothing Then
ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1

'Run macro SortTable
SortTable
End If
End Sub

Sub SortTable()
'Sort table in an ascending order based on first column 
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

Where to put the VBA code?

1. Press with right mouse button on on the sheet name, it is located at the very bottom of the Excel screen.
   
2. Press with left mouse button on "View code".
   
3. Paste event 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

Get the Excel file

Table-and-calendar.xlsm

Calendar category

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

Plot date ranges in a calendar part 2
I will in this article demonstrate a calendar that automatically highlights dates based on date ranges, the calendar populates names […]

Excel calendar [VBA]
Table of Contents Excel monthly calendar - VBA  Calendar Drop down lists Headers Calculating dates (formula) Conditional formatting Today Dates […]

Excel categories

Comments (6)