Dynamically change number formatting within a column

[waho9901]

I have a column that contains a combination of integers and dates (they are changing all the time depending on the source file). I need a way to dynamically code the formatting as such in VBA. I tried creating a length column to base my code off of (i.e. if length = 1 then format as a number or if length =5 then format as date), but I am not successful. Any tips as to how to accomplish this?
Here is my code that is not working:

On Error Resume Next
Rng = Worksheets("Novus DR").Range("AE7")
Dim ImagePath As String
Range("Z7").Select
For i = 1 To Rng
ActiveCell.Offset(1, 0).Select
If i = 5 Then
Selection.NumberFormat = "m/d/yyyy"
ElseIf i = 1 Then
Selection.NumberFormat = "0"
Next i

Any help would be appreciated!

[figment]

i am not sure if thats the best approce to the problem but using that method try this code
Sub testing()
With Worksheets("Novus DR")
For i = 7 To .Range("AE7").End(xlDown).Row
If Len(.Range("AE" & i)) >= 5 Then
.Range("AE" & i).NumberFormat = "m/d/yyyy"
Else
.Range("AE" & i).NumberFormat = "0"
End If
Next
End With
End Sub

[mdmackillop]

Welcome to VBAX


Option Explicit
Sub Test()
Dim Rng As Long
Dim Cel As Range

On Error Resume Next
Rng = Worksheets("NovusDR").Range("AE7")
For Each Cel In Range("Z7").Resize(Rng)
Select Case Len(Cel)
Case 5
Cel.NumberFormat = "m/d/yyyy"
Case 1
Cel.NumberFormat = "0"
End Select
Next
End Sub

[mikerickson]

This uses a change event to adjust the cell formatting to match its contents.
Put in the code module for the desired sheet.


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim oneCell As Range
If Not (Application.Intersect(Target, Target.Parent.Range("Z:Z")) Is Nothing) Then
For Each oneCell In Application.Intersect(Target, Range("Z:Z"))
With oneCell
.NumberFormat = IIf(IsDate(.Value) Or (9 < Val(CStr(.Value))), "m/d/yyyy", "0")
End With
Next oneCell
End If
End Sub