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
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
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments