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
--
HTH...

Jim Thomlinson


"fgwiii" wrote:

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