How to change cells format?

alee001

Board Regular
Joined
Sep 9, 2014
Messages
74
There are many cells are "yyyy-mmm-dd" format in anywhere on worksheet, how to auto detect their change to "dd/mm/yyyy hh:mm" format using VBA?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,771
Office Version
365
Platform
Windows
This should do that for all cells down to the last populated cell on your worksheet.
Code:
Sub MyFormatChange()

    Dim lcell As Range
    Dim cell As Range
    
    Application.ScreenUpdating = False
    
'   Find last cell on worksheet
    Set lcell = Range("A1").SpecialCells(xlLastCell)
    
'   Loop through all cells in workbook
    For Each cell In Range("A1:" & lcell.Address)
'       Change date format
        If cell.NumberFormat = "yyyy-mm-dd" Then cell.NumberFormat = "dd/mm/yyyy hh:mm"
    Next cell
    
    Application.ScreenUpdating = True
    
End Sub
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,202
the newer Excel versions have the option under find (CTRL-F) or find & replace (CTRL-H) to find & replace formats
using the macro recorder on that gives some code such as below. as you can see, no loops needed
Code:
Application.FindFormat.NumberFormat = "yyyy-mm-dd"
Application.ReplaceFormat.NumberFormat = "dd/mm/yyyy hh:mm"
Cells.Replace What:="*", Replacement:="", LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=True, ReplaceFormat:=True
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,771
Office Version
365
Platform
Windows
the newer Excel versions have the option under find (CTRL-F) or find & replace (CTRL-H) to find & replace formats
That's pretty cool. I was unaware of that.
 

alee001

Board Regular
Joined
Sep 9, 2014
Messages
74
Thks Fazza, that is smart and quick code form marco...
Code:
Sub chg_fmt()
    Application.ReplaceFormat.NumberFormat = "dd/mmm/yyyy hh:mm"
    Cells.Replace What:="", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=True, ReplaceFormat:=True
End Sub
 

Forum statistics

Threads
1,085,586
Messages
5,384,592
Members
401,913
Latest member
chethan av

Some videos you may like

This Week's Hot Topics

Top