Find Max day in a month

    Here i am using two functions

  • DAY Function
  • DateSerial Function
  • Executing the program based on CHAGNE EVENT
  •  

    Download The Workbook

     

     

    Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False

    'Define the Range
    Dim Rng As Range
    Set Rng = Range("F3")

    'Fulfill of change event condition
    If Not Intersect(Target, Rng) Is Nothing Then

    'Clear the existing Data
    Range("A2:B13").ClearContents

    MonthNumber = 1
    'Starting the DO Loop

    Do While MonthNumber < 13

    'Printing Month Names in first column
    Cells(MonthNumber + 1, 1).Value = MonthName(MonthNumber, True)

    'Instructed the application to wait for a second in first iteration
    If MonthNumber = 1 Then
    Application.Wait (Now + TimeValue("00:00:01"))
    End If

    'Print Max number of days for each month in second column
    Cells(MonthNumber + 1, 2).Value = Day(DateSerial(Range("F3").Value, MonthNumber + 1, 0))
    MonthNumber = MonthNumber + 1

    Loop
    MsgBox "Hi " & Application.UserName & " Process Completed"
    Application.Speech.Speak ("Hi " & Application.UserName & " Process Completed")

    End If
    Application.EnableEvents = True
    End Sub