Insert row variable
 

Using Excel 97. I created a macro to 1) find a number, 2)Insert a row above,
3) format the row that was inserted and add a name in column B; then move on
to find a specific number and follow steps 2 thru 3 again, but add a page
break above the row inserted. Problem I see with the code is that when
additional records are added in the list, I know that the Range references
after the first find will not be the range as identified in the code. Can
someone help me with the code for the second and third row variables. The
code is listed below. TIA - Jan

Range("U2").Select '(This is the column for the find)
Cells.Find(What:="1", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Selection.EntireRow.Insert
Range("A3:S3").Select '(This will always be the range for the first find)
Range("S3").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 '(This is the column for the find)
Cells.Find(What:="2", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Selection.EntireRow.Insert
Range("A118:S118").Select '(This will a variable row)
Range("S118").Activate
With Selection.Interior
.ColorIndex = 41
.Pattern = xlSolid
End With
Selection.Font.ColorIndex = 2
Range("S117").Select
Selection.Copy
Range("S118").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("B118").Select
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:S133").Select '(This too will be a variable)
Range("S133").Activate
With Selection.Interior
.ColorIndex = 41
.Pattern = xlSolid
End With
Selection.Font.ColorIndex = 2
Range("S132").Select
Selection.Copy
Range("S133").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("B133").Select
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