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

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

CodePudding user response：

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