Alternative to blind formatting

[fgwiii]

Currently I am using a macro to format column E for a number sequence, but I
am receiving new sheets where the info that normally is in column E is in
another column.

Is it possible to modify this code to only format columns in which the
column header is labeled as "Subject ID"?

Columns("E:E").Select
Selection.NumberFormat = "000000000000000 "

End Sub
Sub FormatSheets()
For Each ws In Worksheets
ws.Rows(1).Font.ColorIndex = 2
ws.Columns("E:E").NumberFormat = "000000000000000"
Next ws
End Sub

Thanks

Fred

 

[Bernie Deitrick]

If you are certain that Subject ID is found on every sheet....

Sub FormatSheets()
Dim ws As Worksheet
For Each ws In Worksheets
ws.Cells.Find("Subject ID").EntireColumn.NumberFormat = "000000000000000"
Next ws
End Sub

HTH,
Bernie
MS Excel MVP

 

[Jim Thomlinson]

Try this. It requires you to pass the header row into the function that
returns the appropriate column. This code assumes only one column will be
labeled Subject ID... The error handling is in case there is no Subject ID in
the header row.

Sub FormatSheets()
Dim ws As Worksheet

For Each ws In Worksheets
ws.Rows(1).Font.ColorIndex = 2
On Error Resume Next
SubjectIDColumn(ws.Rows(1)).NumberFormat = "000000000000000"
On Error GoTo 0
Next ws
End Sub

Private Function SubjectIDColumn(ByVal rngHeader As Range) As Range

Set SubjectIDColumn = rngHeader.Find(What:="Subject ID", _
LookAt:=xlWhole, _
LookIn:=xlValues, _
MatchCase:=False).EntireColumn
End Function

 

[fgwiii]

That work fine - but upon a closer look at the column headers I did find
where some of the headers for the subject state "Patient ID" or something
else. Is there a clean way of handling this or should I just create a new
macro for each issue?

Thank you!

Fred

 

[Jim Thomlinson]

That is why the making this a function is so handy. Try the followin change...

Sub FormatSheets()
Dim ws As Worksheet

For Each ws In Worksheets
ws.Rows(1).Font.ColorIndex = 2
On Error Resume Next
SubjectIDColumn(ws.Rows(1), "Subject ID").NumberFormat =
"000000000000000"
SubjectIDColumn(ws.Rows(1), "Patient ID").NumberFormat =
"000000000000000"
On Error GoTo 0
Next ws
End Sub

Private Function SubjectIDColumn(ByVal rngHeader As Range, byval str as
string) As Range

Set SubjectIDColumn = rngHeader.Find(What:=str, _
LookAt:=xlWhole, _
LookIn:=xlValues, _
MatchCase:=False).EntireColumn
End Function

 

[fgwiii]

Jim,

When I attempt to run this it gets errors.

Thanks
Fred

 

[Jim Thomlinson]

The text wrapped in the browser. You need to clean up the added carriage
returns.