follow up: the actual code
 

Sub Macro1()
' Macro1 Macro
' Macro recorded 7/25/2006 by sdziamchuk
'
Dim cell As Range

Dim str As String

Windows("experiment cahs trans.xls").Activate

For Each cell In Range("a2:a40")

If cell = 272244 And cell.Offset(0, 1) < "MISCELLANEOUS" Then

cell.Offset(0, 2).Range("a1:l1").Select
Selection.Copy
Sheets("sheet1").Select
ActiveCell.Offset(9, 0).Range("a1").Select
Selection.Insert shift:=xlDown
Sheets("experiment cahs trans").Select

ElseIf Not IsEmpty(cell.Value) Then

cell.Select
With Selection.Interior
..ColorIndex = 5
..Pattern = xlSolid
End With

Else: Exit Sub


End If
Next cell
End Sub


--
Sergei D