Format Column by text in first row

M

Matt

I have multiple documents with similar data, but not always in the same
format (thanks to too many users touching them). Each document has
multiple sheets and I would like to write a Macro that will format a
particular column to a certain Date Format. I know how to write this
macro if you already know the Column, but it isn't always the same.
This particular column has a header in the first cell, with the data
below it; but for different sheets, it isn't in the same position. For
the first sheet it could be column G and for another it could be column
L. But for all the sheets, the first cell in the column is always the
header ("DateDone") which never changes.

How can I write this macro to find that column and then apply the
format?

Thanks,
Matt Van O'Linda
 
B

Bernard Liengme

This seems to work:

Sub trythis()
Range("A1").Select
Do
Testme = ActiveCell.Value
If Testme = "DateDone" Then Exit Do
ActiveCell.Offset(columnOffset:=1).Activate
Loop
ActiveCell.Offset(rowOffset:=1).Activate
Range(Selection, Selection.End(xlDown)).Select
Selection.NumberFormat = "[$-F800]dddd, mmmm dd, yyyy"
End Sub


Change the last instruction to suit your format needs
best wishes
--


Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email
 
D

Dave Peterson

Option explicit
sub testme()

Dim FoundCell as Range

dim wks as worksheet
for each wks in activeworkbook.worksheets
with wks.rows(1)
set foundcell = .cells.Find(What:="datedone", After:=.cells(.cells.count), _
LookIn:=xlvalues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
if foundcell is nothing then
'not found, what should happen
else
foundcell.entirecolumn.numberformat = "mm/dd/yyyy"
end if
end with
next wks

end sub

This would loop through all the worksheets in the activeworkbook.
 
Top