| Home |
| Search |
| Today's Posts |
|
#19
|
|||
|
|||
Works perfectly. Thankyou very much. "David McRitchie" wrote: What was wanted was a Irregular Color Banding, Color Grouping with Conditional Formatting which in appearance is somewhat similar to Color Banding on Chip Pearson's site http://www.cpearson.com/excel/banding.htm except the areas will be dependent on the data and irregular number of rows. If the column to be checked is B and the existing data is in A through C then a helper column can be created in column D D1: 0 D2: =MOD($D1+($B1<$B2),2) so that we will have 0 or 1, the rows with 1's will be colored by Conditional Formatting. but that formula would not work if the data were sorted or rows were inserted/deleted so all references must be relative to the current row, so rewrite the formula as D2: =MOD(OFFSET($D2,-1,0)+OFFSET($B2,-1,0)<$B2,2) or more likely that a change in column A or in Column B should force a change in the Color Grouping. D2: =MOD(OFFSET($D2,-1,0)+OR(OFFSET($A2,-1,0),OFFSET($B2,-1,0)<$B2),2) Then set up the Conditional Formatting Select a cell on Row 1, then select the column you wish to color such as A through C or use Ctrl+A to select all columns Format, Conditional Formatting condition 1, formulas is: =$D1=1 For more information on Conditional Formatting, and for a pictorial review of this reply see http://www.mvps.org/dmcritchie/excel...t.htm#grouping For more information on the worksheet formulas involved, see your HELP (F1) MOD Worksheet Function OFFSET Worksheet Function For more information specifically on use of OFFSET as used here http://www.mvps.org/dmcritchie/excel/offset.htm --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Linc" wrote in message ... Sent "David McRitchie" wrote: as long as you mark what it is supposed to look like |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Insert rows based on specific value | Excel Worksheet Functions | |||
| REALLY miussing rows in Excel2000 | Excel Discussion (Misc queries) | |||
| Hiding Rows if the linked rows are blank | Excel Discussion (Misc queries) | |||
| Row selections by row # OR by even/odd rows in another spreadsheet | Excel Discussion (Misc queries) | |||
| Adding Rows to Master Sheet | New Users to Excel | |||
Threaded Mode
