If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
conditional format from row to row
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 |
#2
|
|||
|
|||
conditional format from row to row
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 FormatConditional 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 -- Biff Microsoft Excel MVP "John" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|