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"
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.