Number Format by searching word for a Row

[Shreesurya]

Hi,

Can you please help me to write a macro that search first Row and if its finds the word "Date" then the whole column should select and change the Number formatting to Date, and if it finds the word "Time" it should change the formatting for the whole column to Time.

Below is the format of Table where it should change the formatting of Date, Start Time and End Time.

Date	OrderNumber	Start Time	Status	End Time
41841	******	0.994444444	WIP
41842	*****	0.994444444	Completed	0.9955555
41843	*****	0.994444444	Completed	0.9955555
41845	*****	0.994444444	Completed	0.9955555

<tbody>
</tbody>

Thanks in Advance for your Code

Surya Prakash...

 

[BobUmlas]

Sub Converter()
For i = 1 To Range("XFD1").End(xlToLeft).Column
Select Case True
Case InStr(Cells(1, i), "Date") > 0
Columns(i).NumberFormat = "m/d/yy"
Case InStr(Cells(1, i), "Time") > 0
Columns(i).NumberFormat = "h:mm:ss AM/PM"
End Select
Next
End Sub

 

[Shreesurya]

Thank you so much for your Code, it works perfect !

Just one issue, if Cell is updated in lower case (time instead of Time) then the Number format is not changing,

Could you please help me to modify this Code ?