I need to be able to create a macro thast looks for the
column header Date on row 1 and format this entire column
into the date format dd/mm/yy.

The only way I have been able to do this at the moment is
by recording the macro when highlighting all these columns
manually and formatting them. This however only works
because I select the columns myself.

Is there anyway this can work looking for the column
header of Date rather than me having to select them?

Any help would be appreciated - thanks

Sub formatDateCols()
Dim c As Range

For Each c In ActiveSheet.Range("1:1")
If c = "Date" Then
c.EntireColumn.NumberFormat = "dd/mm/yy"
End If
Next
End Sub


You only need to ask once.

Chrissy.


Sarah wrote
I need to be able to create a macro thast looks for the
column header Date on row 1 and format this entire column
into the date format dd/mm/yy.

The only way I have been able to do this at the moment is
by recording the macro when highlighting all these columns
manually and formatting them. This however only works
because I select the columns myself.

Is there anyway this can work looking for the column
header of Date rather than me having to select them?

Any help would be appreciated - thanks

A possible alleviation of the problem you might have as you stated in your
next post:

Sub formatDateCols()
Dim c As Range

For Each c In ActiveSheet.Range("1:1")
If Instr(1,c, "date",vbTextCompare) Then
c.EntireColumn.NumberFormat = "dd/mm/yy"
End If
Next
End Sub


or

Sub formatDateCols()
Dim c As Range

For Each c In ActiveSheet.Range("1:1")
If strcomp(c, "date",vbTextCompare) = 0 Then
c.EntireColumn.NumberFormat = "dd/mm/yy"
End If
Next
End Sub

--
Regards,
Tom Ogilvy

"Chrissy" wrote in message
...
Sub formatDateCols()
Dim c As Range

For Each c In ActiveSheet.Range("1:1")
If c = "Date" Then
c.EntireColumn.NumberFormat = "dd/mm/yy"
End If
Next
End Sub


You only need to ask once.

Chrissy.


Sarah wrote
I need to be able to create a macro thast looks for the
column header Date on row 1 and format this entire column
into the date format dd/mm/yy.

The only way I have been able to do this at the moment is
by recording the macro when highlighting all these columns
manually and formatting them. This however only works
because I select the columns myself.

Is there anyway this can work looking for the column
header of Date rather than me having to select them?

Any help would be appreciated - thanks

Tom,

Care to discuss and compare the three approaches shown here?

Chrissy.

Tom Ogilvy wrote
A possible alleviation of the problem you might have as you stated in your
next post:

Sub formatDateCols()
Dim c As Range

For Each c In ActiveSheet.Range("1:1")
If Instr(1,c, "date",vbTextCompare) Then
c.EntireColumn.NumberFormat = "dd/mm/yy"
End If
Next
End Sub


or

Sub formatDateCols()
Dim c As Range

For Each c In ActiveSheet.Range("1:1")
If strcomp(c, "date",vbTextCompare) = 0 Then
c.EntireColumn.NumberFormat = "dd/mm/yy"
End If
Next
End Sub


Sub formatDateCols()
Dim c As Range

For Each c In ActiveSheet.Range("1:1")
If c = "Date" Then
c.EntireColumn.NumberFormat = "dd/mm/yy"
End If
Next
End Sub

The original is case sensative. Since the question was posted twice, a
similar answer was posted in response to the second and the OP came back and
said the code did nothing. My assumption was that it was a case issue.

first I posted is case insensitive and looks for the string "date" in
content of the cell, so it could be too broad. However, it would work with
spaces on either or both ends.

the second is case insensitive and looks for the exact term date. It would
falter if there were spaces on either or both ends


--
Regards,
Tom Ogilvy


"Chrissy" wrote in message
...
Tom,

Care to discuss and compare the three approaches shown here?

Chrissy.

Tom Ogilvy wrote
A possible alleviation of the problem you might have as you stated in
your
next post:

Sub formatDateCols()
Dim c As Range

For Each c In ActiveSheet.Range("1:1")
If Instr(1,c, "date",vbTextCompare) Then
c.EntireColumn.NumberFormat = "dd/mm/yy"
End If
Next
End Sub


or

Sub formatDateCols()
Dim c As Range

For Each c In ActiveSheet.Range("1:1")
If strcomp(c, "date",vbTextCompare) = 0 Then
c.EntireColumn.NumberFormat = "dd/mm/yy"
End If
Next
End Sub


Sub formatDateCols()
Dim c As Range

For Each c In ActiveSheet.Range("1:1")
If c = "Date" Then
c.EntireColumn.NumberFormat = "dd/mm/yy"
End If
Next
End Sub