How to change cells format?

[alee001]

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]

This should do that for all cells down to the last populated cell on your worksheet.

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

 

[alee001]

Thanks you very much for your help.

 

[Fazza]

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

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]

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]

Thks Fazza, that is smart and quick code form marco...

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