Using Excel 97. I have a list of records that once sorted I want to be able
to do the following.
1). Find the first occurence of "1" in column U
2). Insert a row above
3). Select blank row from columns A:R
4). Shade cells
5). Place text in Column B of blank row.

Then find the first occurence of "2" in column U and proceed with steps 2
thru 5 above. Then proceed to find first occurence of "3" in column u and
proceed with steps 2 thru 5 above.
This is a process that will need to be done weekly so I would really like to
just run a macro to do the process. However, because I am working with a
list the number of rows will change weekly. The current code below generated
via the macro function uses specific row/column ranges, e.g. A137:R137. I
see the specific ranges in the code as a problem since the list will grow.
Can some help me revise the code so that when I run the macro there will not
be an issue? Thank you.

Sub Order()
Range("U2").Select
Cells.Find(What:="1", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Selection.EntireRow.Insert
Range("A3:r3").Select '(This will always be the first range)
Range("r3").Activate
With Selection.Interior
.ColorIndex = 41
.Pattern = xlSolid
End With
Selection.Font.ColorIndex = 2
Range("B3").Select
ActiveCell.FormulaR1C1 = "MIKE"
With ActiveCell.Characters(Start:=1, Length:=4).Font
.Name = "Arial"
.FontStyle = "Bold Italic"
.Size = 14
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
End With
Range("U2").Select
Cells.Find(What:="2", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Selection.EntireRow.Insert
Range("A118:r118").Select '(This will change when the list grows)
Range("r118").Activate
With Selection.Interior
.ColorIndex = 41
.Pattern = xlSolid
End With
Selection.Font.ColorIndex = 2
Range("r117").Select
Selection.Copy
Range("r118").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("B118").Select '(this won't always be the row)
ActiveCell.FormulaR1C1 = "MAM"
With ActiveCell.Characters(Start:=1, Length:=3).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 14
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
End With
Range("A118").Select
ActiveWindow.SelectedSheets.HPageBreaks.Add Befo=ActiveCell
Range("U2").Select
Cells.Find(What:="3", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Selection.EntireRow.Insert
Range("A133:r133").Select '(The row reference will change as the list
grows)
Range("r133").Activate
With Selection.Interior
.ColorIndex = 41
.Pattern = xlSolid
End With
Selection.Font.ColorIndex = 2
Range("r132").Select
Selection.Copy
Range("r133").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("B133").Select '(This too will not always be the row)
ActiveCell.FormulaR1C1 = "TO PRINTER"
With ActiveCell.Characters(Start:=1, Length:=10).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 14
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
End With
Range("A133").Select
ActiveWindow.SelectedSheets.HPageBreaks.Add Befo=ActiveCell
Range("U2").Select
Selection.AutoFilter Field:=21, Criteria1:="<4", Operator:=xlAnd
End Sub