Create a 12 Month Calendar With The Current Day Highlighted in Excel

Add to Favorites
Create a 12 month formatted calendar on a new tab in Excel with the current day highlighted. This macro creates a nicely formatted 12 month calendar where all days in every month are listed and in date format. This means that you can immediately perform date related functions and formulas off of the dates created from this macro and calendar.

This macro saves you a lot of time if you need to have a calendar display in Excel. This macro is also very easy to change in terms of formatting and this is good if you want to change month heading colors or cell background colors etc.

Where to install the macro:  Module

Excel Macro to Create a 12 Month Calendar With The Current Day Highlighted in Excel

Sub CreateCalendar()

Dim lMonth As Long

Dim strMonth As String

Dim rStart As Range

Dim strAddress As String

Dim rCell As Range

Dim lDays As Long

Dim dDate As Date

'Add new sheet and format

Worksheets.Add

ActiveWindow.DisplayGridlines = False

With Cells

.ColumnWidth = 6#

.Font.Size = 8

End With

 

'Create the Month headings

For lMonth = 1 To 4

Select Case lMonth

Case 1

strMonth = "January"

Set rStart = Range("A1")

Case 2

strMonth = "April"

Set rStart = Range("A8")

Case 3

strMonth = "July"

Set rStart = Range("A15")

Case 4

strMonth = "October"

Set rStart = Range("A22")

End Select

'Merge, AutoFill and align months

With rStart

.Value = strMonth

.HorizontalAlignment = xlCenter

.Interior.ColorIndex = 6

.Font.Bold = True

With .Range("A1:G1")

.Merge

.BorderAround LineStyle:=xlContinuous

End With

.Range("A1:G1").AutoFill Destination:=.Range("A1:U1")

End With

Next lMonth

'Pass ranges for months

For lMonth = 1 To 12

strAddress = Choose(lMonth, "A2:G7", "H2:N7", "O2:U7", _
"A9:G14", "H9:N14", "O9:U14", _
"A16:G21", "H16:N21", "O16:U21", _
"A23:G28", "H23:N28", "O23:U28")

lDays = 0

Range(strAddress).BorderAround LineStyle:=xlContinuous

'Add dates to month range and format

For Each rCell In Range(strAddress)

lDays = lDays + 1

dDate = DateSerial(Year(Date), lMonth, lDays)

If Month(dDate) = lMonth Then ' It's a valid date

With rCell

.Value = dDate

.NumberFormat = "ddd dd"

End With

End If

Next rCell

Next lMonth

'add con formatting

With Range("A1:U28")

.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=TODAY()"

.FormatConditions(1).Font.ColorIndex = 2

.FormatConditions(1).Interior.ColorIndex = 1

End With

End Sub







Similar Content on TeachExcel
Display the Current Day of the Week in Excel
Tutorial: Use a formula or formatting to display the current day of the week in Excel. Sections: For...
Print The Current Worksheet in Excel
Macro: This free Excel macro will print the current active worksheet in Excel. This means th...
Print Entire Workbook in Excel
Macro: This free excel macro allows you to print the entire workbook in Excel. You can easil...
Select Cells in Excel using Macros and VBA
Tutorial: This is actually a very easy thing to do and only requires a couple lines of code. Below I...
Require a Unique List of Numbers in a Range in Excel
Tutorial: I'll show you how to require a user to enter a unique number into a range of cells in Exce...
Disables the "Save As" Feature in Excel
Macro: This macro will disable the Save As feature in excel. This means that a user will not...



How to Install the Macro
  1. Select and copy the text from within the grey box above.

  2. Open the Microsoft Excel file in which you would like the Macro to function.

  3. Press "Alt + F11" - This will open the Visual Basic Editor - Works for all Excel Versions.  Or For other ways to get there, Click Here.

  4. On the new window that opens up, go to the left side where the vertical pane is located. Locate your Excel file; it will be called VBAProject (YOUR FILE'S NAME HERE) and click this.

  5. If the Macro goes in a Module, Click Here, otherwise continue to Step 8.

  6. If the Macro goes in the Workbook or ThisWorkbook, Click Here, otherwise continue to Step 8.

  7. If the Macro goes in the Worksheet Code, Click Here, otherwise continue to Step 8.

  8. Close the Microsoft Visual Basic Editor window and save the Excel file. When you close the Visual Basic Editor window, the regular Excel window will not close.

  9. You are now ready to run the macro.


Similar Content
Display the Current Day of the Week in Excel
Tutorial: Use a formula or formatting to display the current day of the week in Excel. Sections: For...
Print The Current Worksheet in Excel
Macro: This free Excel macro will print the current active worksheet in Excel. This means th...
Print Entire Workbook in Excel
Macro: This free excel macro allows you to print the entire workbook in Excel. You can easil...
Excel Forum