Conditional Formatting for an unknown range

[BaseballFan]

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.

 

[T. Valko]

In other words, you want to format cells that *don't contain the word StopX"
?

Select the *entire* range of cells starting from cell A1.
Goto the menu Format>Conditional Formatting
Select the Formula Is option
Enter this formula in the box on the right:
=COUNTIF(A1,"stop*")=0
Click the Format button
Select the desired style(s)
OK out

 

[Don Guillett]

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
=============

 

[BaseballFan]

In other words, you want to format cells that *don't contain the word StopX"

Sort of... I want to make the first range yellow, the second range blue, and
the third range green. The first range will be A1:xx23 (xx being 1 row above
wherever the first "Stop" text might be). The second range will be yy1:zz25
(yy being 1 row below the first "Stop" text, and zz being 1 row above
wherever the 2nd "Stop" might be)

Another "hard" part is that there will not be a "final" 'Stop' text. I want
to highlight the 3rd range of data after the 2nd "Stop" text, but finish
highlighting at the final row of data.

Thanks.
Jim

 

[Don Guillett]

Modify my first post to this.
'========
Public lastrow
Sub findstops()
bottomrow = Cells(Rows.Count, "a").End(xlUp).Row
startrow = 1
colorcount = 1
With Range("a1:a" & bottomrow)
Set c = .Find("stop", LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do

Select Case colorcount
Case 1: x = 4 'change numbers to suit
Case 2: x = 5
Case 3: x = 6
Case Else
End Select

Range(Cells(startrow, "a"), Cells(c.Row - 1, "a")) _
.Interior.ColorIndex = x
colorcount = colorcount + 1
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
'===========

 

[BaseballFan]

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

Hmmmm... I think that's above my skill set. :/

 

[T. Valko]

Ok, try this:

Assumptions:

There will *always* be a Stop1 and a Stop2.
There will *never* be duplicate Stops. Like 2 instances of Stop1 or Stop2.

Select the *entire* range that you want to format starting from cell A1. If
you don't know how many rows of data you'll have then select a range that is
larger than you will ever use (but not too large!). In the formulas I'm
suggesting I use the range from A1:A20. Adjust the end of that range as
needed.

Conditional Formatting
Condition 1
Formula Is:

=ROW(A1)<MATCH("stop1",A:A)

Click the Format button
Select the Patterns tab
Select a nice shade of YELLOW
OK

Click the Add button

Condition 2
Formula Is:

=AND(ROW(A1)>MATCH("stop1",A:A,0),ROW(A1)<MATCH("stop2",A:A,0))

Click the Format button
Select the Patterns tab
Select a nice shade of BLUE
OK

Click the Add button

Condition 3
Formula Is:

=AND(ROW(A1)>MATCH("stop2",A:A,0),ROW(A1)<=MAX((A$1:A$20<>"")*ROW(A$1:A$20)))

Click the Format button
Select the Patterns tab
Select a nice shade of GREEN
OK out

If you test these formulas *on the worksheet* the formula for condition 3 is
an array formula. Array formulas are entered differently (on the worksheet)
than a regular formula. After you type in a regular formula you hit the
ENTER key. With an array formula you *must* use a combination of keys. Those
keys are the CTRL key, the SHIFT key and the ENTER key. That is, hold down
both the CTRL key and the SHIFT key then hit the ENTER key.

When done properly Excel will enclose the formula in squiggly brackets { }.
You can't just type these brackets in, you *must* use the key combo to
produce them. Also, anytime you edit an array formula it *must* be
re-entered as an array using the key combo.

 

[Don Guillett]

Try it??
Or, send your file to me and I'll have a look. Be sure to identify what you
are talking about as I get many emails.