Put this in a regular module and execute it to see what happens. Then change
..copy to whatever desired and comment out the msgbox line
'=============
Public lastrow
Sub findstops()
bottomrow=cells(rows.count,"a").end(xlup).row
startrow= 1
With Range("a1:a" & bottomrow)
Set c = .Find("stop", LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do

Range(Cells(startrow, "a"), Cells(c.Row - 1, "a")).Copy

MsgBox c.Row
startrow = c.Row + 1
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With
End Sub
=============
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"BaseballFan" wrote in message
...
I'd like to conditionally format a range of cells, but the range is
dynamic.
For instance, I'd like to format A1:A20, but I only know to stop at A20
because cell A21 has the text "Stop1". I'd then like to format A22:A40,
but
I only know to start at A22 and stop at A40 because it's after the Stop1
A41
has the text "Stop2".

Where "Stop1" and "Stop2" are actually at is dynamic... Stop1 may appear
at
A5 on a short list of items, but may go all the way down to A100 on a
longer
list.

Essentially, I'd like to format downward until I hit that "Stop#" value,
and
then start again until I hit the next stop value.