Doesn't change the format of columns to date

142 views Asked by At

Can any one point out what's wrong with the part where it changes the format of the columns to Date? It worked before but now for some reason it doesn't change the format anymore. Thanks in advance.

   With wsMain
       .Columns("A:AO").AutoFit
       .Cells.ClearFormats
       .Rows(1).Font.Bold = True
       .Cells.Font.Name = "Georgia"
       .Cells.Font.Color = RGB(0, 0, 225)
       .Cells.Resize(.Rows.Count - 1).Offset(1).Interior.Color = RGB(216, 228, 188)
       .Columns(9).NumberFormat = "MM/DD/YYYY"
       .Columns(11).NumberFormat = "MM/DD/YYYY"
       .Columns(17).NumberFormat = "MM/DD/YYYY"
       .Columns(18).NumberFormat = "MM/DD/YYYY"
       .Columns(20).NumberFormat = "MM/DD/YYYY"
       .Columns(22).NumberFormat = "MM/DD/YYYY"
       .Columns(23).NumberFormat = "MM/DD/YYYY"
       .Columns(29).NumberFormat = "MM/DD/YYYY"
1

There are 1 answers

0
joehanna On

I suspect the data is still marked as "Text" despite the column formatting. I bet if you do a Find/Replace on that column by replacing "/" with "/" (yes - replace a slash with the same slash character), Excel will recognise the newly replaced values as dates and format them appropriately. It's a quirk in Excel. If possible, you should be running the NumberFormat code before you introduce data in those columns.