conditional format from row to row

[John]

Hi, I am using Excel 2003 and would like a conditional format to work with a
named range, is that possible? I have a named Range called City in column b.
When the city changes I would like for the entire row to change to a specific
color I designate. I have used this formula =$b16<>$b15 and this works until
I filter the data. Please if anyone has done this or something close I would
like to see your formula.

Thanks,
John

 

[T. Valko]

For a "2 color" band...one group of rows will be the color you select. The
next group of rows will not be colored in effect giving you a 2 color band.

Let's assume the range you want to format is A2:B16. You want to color band
the range when there is a change in column B.

A1:B1 are the column headers.

Select the range A2:B16
Goto the menu Format>Conditional Formatting
Select the Formula Is option
Enter this formula in the box on the right

=MOD(SUMPRODUCT(SUBTOTAL(3,OFFSET($B$2:$B2,ROW(B$2:B2)-ROW(B$2),0,1)),--($B$1:$B1<>$B$2:$B2)),2)

Click the Format button
Select the desired fill color
OK out

This will be slow to calculate on large amounts of data.

Sample file available on request