Wanting all cells that are supposed to be dates to be in MM/DD/YY format

[Aaron DOJ]

I am trying to get just the date fields in my spreadsheet to always be MM/DD/YY format no matter what kind of date is entered such as 1/1 will come out 01/01/18 or 1 jan will change to 01/01/18 etc. For the most part I got it to work with the codes below, the problem I am now encountering, that I can't seem to think how to stop is any other field with numbers or text from changing to a date format...can someone help me please

Worksheet

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If IsEmpty(Cells(2, 2)) Then 'if empty, put in "Value" and make value change text to teal.
    Cells(2, 2).Value = "MM/DD/YY"
    Cells(2, 2).Font.ColorIndex = 14
ElseIf Sheet1.Cells(2, 2).Value = "MM/DD/YY" Then 'if equal to "Value", change text to teal.
    Cells(2, 2).Font.ColorIndex = 14
ElseIf Sheet1.Cells(2, 2).Value <> "MM/DD/YY" Then 'if text other than "Value", change text to black.
    Call Format_Dates
    Cells(2, 2).Font.ColorIndex = 1
End If
   
If IsEmpty(Cells(3, 2)) Then 'if empty, put in "Value" and make value change text to teal.
    Cells(3, 2).Value = "MM/DD/YY"
    Cells(3, 2).Font.ColorIndex = 14
ElseIf Sheet1.Cells(3, 2).Value = "MM/DD/YY" Then 'if equal to "Value", change text to teal.
    Cells(3, 2).Font.ColorIndex = 14
ElseIf Sheet1.Cells(3, 2).Value <> "MM/DD/YY" Then 'if text other than "Value", change text to black.
    Call Format_Dates
    Cells(3, 2).Font.ColorIndex = 1
End If
   
End Sub

Module

Sub Format_Dates()
    Selection.NumberFormat = "mm/dd/yy"
End Sub

 

[Aaron DOJ]

I can make it always be (X10) "MM/DD/YY -" or (Y10) "- MM/DD/YY"

 

[Yongle]

The simplest way is format cells as per the original macro and then format Y10 again

Add this line towards the end of your code (outside the formatting loop)

 Range("Y10").NumberFormat = "- mm/dd/yy"

 

[Aaron DOJ]

Perfect, that should work!! Thank you Yongle!!