Macro to format date

[sw]

I am looking for some code that will look through my
spreadsheet, find all columns headers called "Date" (which
will always be in row 1) and on finding a date column
format all cells in that column to the date format
dd/mm/yy.

Can anyone help with this.

Thanks

 

[Ecco]

Try this one.

Sub Macro1()
Dim X As Long
For X = 1 To 256
If Cells(1, X) = "Date" Then
Columns(X).NumberFormat = "dd/mm/yy"
End If
Next X
End Sub

Ecco

 

[sw]

I entered this code as it is stated below and ran the
macro but nothing happened! Any idea why

 

[Ecco]

Check if title Date is really written like Data (not like
date or DATE). You could change the IF-statement like this:

If Lcase(Cells(1,X))="date" Then

That´s the only idea I come up with. Hope it works.

Ecco

 

[Tom Ogilvy]

Sub Macro1()
Dim X As Long
For X = 1 To 256
If Instr(1,Cells(1, X).Value,"date",vbTextCompare) Then
Columns(X).NumberFormat = "dd/mm/yy"
End If
Next X
End Sub