Fill date (jj/mm/yyyy) only by entering the day (jj)

Question

I have 12 sheets for 12 months with random monthly dates to manually enter in column A. Let's take the month of January as an example:

When I enter the number 25 in cell A1, I'd like the cell to automatically return 25/01/2019 in A1 (!) (or 01/25/2019, as you like). Excel autofill features can't do that to my knowledge even with custom settings, so I guess : VBA ?

I think the code should look something like this (with change event):

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range, cell As Range
    Set rng = Range("A:A")
    If Not Application.Intersect(rng, Range(Target.Address)) _
           Is Nothing Then

           '???
           'If cell entered in range is a number Then
           'change value to "number" & "/01/2019" 

    End If
End Sub

That's where I am at. I'm pretty sure this could be a useful piece of code for people working with month and entering many dates. I'm I far from the truth ? Is it even do-able ? I understand it might be more complicated than it sounds.

Answer 1

Option Explicit    

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim AffectedRange As Range
    Set AffectedRange = Application.Intersect(Me.Range("A:A"), Target)

    If Not AffectedRange Is Nothing Then
        Dim Cell As Range
        For Each Cell In AffectedRange.Cells
            If Cell.Value >= 1 And Cell.Value <= 31 Then
                Application.EnableEvents = False
                Cell.Value = DateSerial(2019, 1, Cell.Value)
                Cell.NumberFormat = "YYYY-MM-DD"
                Application.EnableEvents = True
            End If
        Next Cell
    End If
End Sub