Automatically converting MO/00/YR to the last day of the month

Sharon_Dquest1 

Right-click the sheet tab.

Select 'View Code' from the context menu.

Copy the following code into the worksheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim c As Range
    Dim m As Long
    Dim y As Long
    If Not Intersect(Range("E4:E10000"), Target) Is Nothing Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        For Each c In Intersect(Range("E4:E10000"), Target)
            If Mid(c.Value, 3, 4) = "/00/" Then
                m = Left(c.Value, 2)
                y = 2000 + Right(c.Value, 2)
                c.Value = DateSerial(y, m + 1, 0)
            End If
        Next c
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub

Switch back to Excel.

Save the workbook as a macro-enabled workbook.

Make sure that you allow macros when you open it.