Macro Formatting Issue

[awappel]

Sub FormatOnly()
colText = "B,E,G,H,I,J,K,L,M,O,R,S,X,Y,AA,AC,AE,AG,AJ,AR,AS,AU,AV,AW,AX,AY,AZ,BA,BC,BE,BF,BG"
colNumber = "C,D,F,N,P,Q,T,U,V,W,Z,AB,AD,AF,AH,AI,AK,AL,AM,AN,AO,AP,AQ,AT,BB,BD"

Sheets("Listing Template").Select
' Member ID
Columns("A:A").Select
Selection.NumberFormat = "00000"
' Property Type
Columns("E:E").Select
Selection.NumberFormat = "00"
' Payment Frequency
Columns("O:O").Select
Selection.NumberFormat = "00"
' Interest Rate Index
Columns("R:R").Select
Selection.NumberFormat = "00"
' Text
slist = Split(colText, ",")
For I = 0 To UBound(slist)
Columns(slist(I) & ":" & slist(I)).Select
Selection.NumberFormat = "@"
Next I
' Numeric
slist = Split(colNumber, ",")
For I = 0 To UBound(slist)
Columns(slist(I) & ":" & slist(I)).Select
Selection.NumberFormat = "0"
Next I
Range("A1").Select
End Sub

The above macro formats my dates as a text field, which is what I need, but then it also transforms the dates into a 5 digit format that isn't readable. Any ideas why that is happening.

 

[Joe4]

Date are actually stored in Excel as the number of days since 1/1/1900 (to see this, type any date into Excel and then change its format to any Number or General format).

How do you want the date formatted?
Which column is it in?
Are all entries in that column dates?

 

[awappel]

I want the date formatted YYYYMMDD and then the macro should turn it into a text. All items in columns G, H, M, AG, AJ and BG are dates.

 

[Joe4]

Changing the formatting of the cell does not change the nature of the value stored in it (it will still remain a numeric date value and not text).

If you wish to convert a date entry to text in VBA in YYYYMMDD format, then use the FORMAT function, i.e.

Range("A1")=FORMAT(Range("A1"),"yyyymmdd")