Home Change Date format to (dd-mmm-yy) if the inserted values are Date , Not Work?

Questions

Change Date format to (dd-mmm-yy) if the inserted values are Date , Not Work?

December 23, 2021

I need Change Date format to (dd-mmm-yy) if the inserted values are Date ,But it is Not Work !

the current result format is dd-mm-yy and the expected is dd-mmm-yy.
I tried the below code. as always,any help will be appreciated.

Private Sub Worksheet_Change(ByVal Target As Range)

  If Not Intersect(Target, Columns("E:F")) Is Nothing Then  'Change Date format to dd-mmm-yy
  
        If Not IsDate(Target.value) = False Then
         
         Target.NumberFormat = "dd-mmm-yy"
         
         End If
       End If

End Sub

>Solution :

You probably need to loop, if you’re changing multiple cells at once. Note that changing the number format doesn’t change the underlying value. If you’re working with text-that-looks-like-a-date, changing the number format won’t do anything. You’d need to convert it to a date first (maybe using CDate or DateValue).

Dim rng As Range
Set rng = Intersect(Target, Columns("E:F"))

If Not rng Is Nothing Then
   Dim cell As Range
   For Each cell In rng
       If IsDate(cell.Value) Then
           cell.NumberFormat = "dd-mmm-yy"
       End If
   Next
End If