keeping a custom format date

[will1128]

I have columns of various types which need different formats. I'm aware Excel treats dates as a number, but I have a column which has dates as custom type in the format "M/D/YYYY H:MM". If I find a cell like this I need to keep the date. I've used a case statement for the other formatting & it seems to work. How do I keep my custom date format?

<code>
For Each ws In ActiveWorkbook.Worksheets
For Each c In ws.UsedRange
Select Case c.value
Case 0, 1, 2
c.NumberFormat = "0"
Case c.value = "M/DD/YYYY H:MM"
c.value = c.value
Case Else
c.NumberFormat = "0.0"
End Select
Next c
Next ws
</code>

 

[VoG]

Try

For Each ws In ActiveWorkbook.Worksheets
For Each c In ws.UsedRange
    If IsDate(c.Value) Then
        c.NumberFormat = "M/DD/YYYY H:MM"
    Else
        Select Case c.Value
            Case 0, 1, 2: c.NumberFormat = "0"
            Case Else: c.NumberFormat = "0.0"
        End Select
    End If
Next c
Next ws

 

[will1128]

Thank you.

I had tried using IsDate(c.value) = True, but that didn't work....why is the If statement necessary?

 

[VoG]

As far as I know IsDate() is read only so you need a If statement to check its value.