Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alternative to blind formatting
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alternative to blind formatting
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 "fgwiii" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alternative to blind formatting
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alternative to blind formatting
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" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alternative to blind formatting
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 -- HTH... Jim Thomlinson "fgwiii" wrote: 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" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alternative to blind formatting
Jim,
When I attempt to run this it gets errors. Thanks Fred "Jim Thomlinson" wrote: 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 -- HTH... Jim Thomlinson "fgwiii" wrote: 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" wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alternative to blind formatting
The text wrapped in the browser. You need to clean up the added carriage
returns. -- HTH... Jim Thomlinson "fgwiii" wrote: Jim, When I attempt to run this it gets errors. Thanks Fred "Jim Thomlinson" wrote: 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 -- HTH... Jim Thomlinson "fgwiii" wrote: 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" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Set Range.....am I to blind? | Excel Programming | |||
Excel add-in that may help the color blind | Excel Programming | |||
Blind Carbon Copy (Bcc) | Excel Discussion (Misc queries) | |||
Excel for Blind persons use | Excel Discussion (Misc queries) | |||
Alternative to Conditional Formatting | Excel Programming |