Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Banding groups
For readability, I would like to be able to band a spreadsheet that has been
grouped. Either of the following solutions would work... Ex: Ford Mustang F-150 General Motors Corvette Silverado Honda S2000 Ridgeline Element 1. (Preferred) Alternate grey (G) and white (W) background on rows at level 1 and have all child rows inherit that background color. (G) Ford (G) Mustang (G) F-150 (W)General Motors (W) Corvette (W) Silverado (G)Honda (G) S2000 (G) Ridgeline (G) Element 2. (Acceptable) Set a grey background for all rows at level 1 and a white background for all child rows. (G) Ford (W) Mustang (W) F-150 (G)General Motors (W) Corvette (W) Silverado (G)Honda (W) S2000 (W) Ridgeline (W) Element Obviously, this involves Conditional Formatting, but how can I ask a row if it's group level or how can I band child rows to follow their parent row when the parent rows alternate background color? Thanks! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Banding groups
Try this:
Assuming your data starts in cell A2. Select the range of cells in question Goto the menu FormatConditional Formatting Formula Is: =MOD(COUNTA(A$2:A2),2)=1 Click the format button Set the fill color to a shade of gray OK out -- Biff Microsoft Excel MVP "Habanerotary" wrote in message ... For readability, I would like to be able to band a spreadsheet that has been grouped. Either of the following solutions would work... Ex: Ford Mustang F-150 General Motors Corvette Silverado Honda S2000 Ridgeline Element 1. (Preferred) Alternate grey (G) and white (W) background on rows at level 1 and have all child rows inherit that background color. (G) Ford (G) Mustang (G) F-150 (W)General Motors (W) Corvette (W) Silverado (G)Honda (G) S2000 (G) Ridgeline (G) Element 2. (Acceptable) Set a grey background for all rows at level 1 and a white background for all child rows. (G) Ford (W) Mustang (W) F-150 (G)General Motors (W) Corvette (W) Silverado (G)Honda (W) S2000 (W) Ridgeline (W) Element Obviously, this involves Conditional Formatting, but how can I ask a row if it's group level or how can I band child rows to follow their parent row when the parent rows alternate background color? Thanks! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Banding groups
Correction:
I'm assuming that the make is in one column and the models are in another column (that's how I'm interpreting the offset in your posted sample) Change the formula to: =MOD(COUNTA($A$2:$A2),2)=1 Screencap: http://img262.imageshack.us/img262/1954/cfzg7.jpg -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Try this: Assuming your data starts in cell A2. Select the range of cells in question Goto the menu FormatConditional Formatting Formula Is: =MOD(COUNTA(A$2:A2),2)=1 Click the format button Set the fill color to a shade of gray OK out -- Biff Microsoft Excel MVP "Habanerotary" wrote in message ... For readability, I would like to be able to band a spreadsheet that has been grouped. Either of the following solutions would work... Ex: Ford Mustang F-150 General Motors Corvette Silverado Honda S2000 Ridgeline Element 1. (Preferred) Alternate grey (G) and white (W) background on rows at level 1 and have all child rows inherit that background color. (G) Ford (G) Mustang (G) F-150 (W)General Motors (W) Corvette (W) Silverado (G)Honda (G) S2000 (G) Ridgeline (G) Element 2. (Acceptable) Set a grey background for all rows at level 1 and a white background for all child rows. (G) Ford (W) Mustang (W) F-150 (G)General Motors (W) Corvette (W) Silverado (G)Honda (W) S2000 (W) Ridgeline (W) Element Obviously, this involves Conditional Formatting, but how can I ask a row if it's group level or how can I band child rows to follow their parent row when the parent rows alternate background color? Thanks! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Banding groups
The following will create bands, alternating between no bands and light-gray
bands, grouped by data (or lack thereof) in column A. As in your example, the Fords will be gray, GM will be uncolored, Honda will be gray, and so on. The bands need not have the same number of rows. Change the lines of code marked with '<<< to suit your specific needs. Sub AAA() Dim LastRow As Long Dim FirstRow As Long Dim RowNdx As Long Dim Band As Boolean Const COLUMNS_TO_BAND = 10 '<<< Change to number of columns wide to shade Const BAND_COLORINDEX = 15 '<<< 15 = Light gray Band = False '<<< False to have first band grey, True to have first band white FirstRow = 1 '<<< Change to first row number of data to band. LastRow = Cells(Rows.Count, "B").End(xlUp).Row '<<< change "B" to sub data column For RowNdx = 1 To LastRow If Cells(RowNdx, "A").Value = vbNullString Then If Band Then Cells(RowNdx, "A").Resize(1, COLUMNS_TO_BAND) _ .Interior.ColorIndex = BAND_COLORINDEX 'light grey End If Else Band = Not Band If Band Then Cells(RowNdx, "A").Resize(1, COLUMNS_TO_BAND) _ .Interior.ColorIndex = BAND_COLORINDEX 'light grey End If End If Next RowNdx End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting www.cpearson.com (email on the web site) "Habanerotary" wrote in message ... For readability, I would like to be able to band a spreadsheet that has been grouped. Either of the following solutions would work... Ex: Ford Mustang F-150 General Motors Corvette Silverado Honda S2000 Ridgeline Element 1. (Preferred) Alternate grey (G) and white (W) background on rows at level 1 and have all child rows inherit that background color. (G) Ford (G) Mustang (G) F-150 (W)General Motors (W) Corvette (W) Silverado (G)Honda (G) S2000 (G) Ridgeline (G) Element 2. (Acceptable) Set a grey background for all rows at level 1 and a white background for all child rows. (G) Ford (W) Mustang (W) F-150 (G)General Motors (W) Corvette (W) Silverado (G)Honda (W) S2000 (W) Ridgeline (W) Element Obviously, this involves Conditional Formatting, but how can I ask a row if it's group level or how can I band child rows to follow their parent row when the parent rows alternate background color? Thanks! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Banding groups
Biff,
Thank you, I can make that work. I was hoping not to have to have my child rows start in column B, but I can see how my layout might have given you that impression. I've actually indented the contents of each of the columns. Is there some way to detect the level of a row within its grouping? Then identifying the parent row would make the conditional formating straightforward. Thanks so much for your response! "T. Valko" wrote: Correction: I'm assuming that the make is in one column and the models are in another column (that's how I'm interpreting the offset in your posted sample) Change the formula to: =MOD(COUNTA($A$2:$A2),2)=1 Screencap: http://img262.imageshack.us/img262/1954/cfzg7.jpg -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Try this: Assuming your data starts in cell A2. Select the range of cells in question Goto the menu FormatConditional Formatting Formula Is: =MOD(COUNTA(A$2:A2),2)=1 Click the format button Set the fill color to a shade of gray OK out -- Biff Microsoft Excel MVP "Habanerotary" wrote in message ... For readability, I would like to be able to band a spreadsheet that has been grouped. Either of the following solutions would work... Ex: Ford Mustang F-150 General Motors Corvette Silverado Honda S2000 Ridgeline Element 1. (Preferred) Alternate grey (G) and white (W) background on rows at level 1 and have all child rows inherit that background color. (G) Ford (G) Mustang (G) F-150 (W)General Motors (W) Corvette (W) Silverado (G)Honda (G) S2000 (G) Ridgeline (G) Element 2. (Acceptable) Set a grey background for all rows at level 1 and a white background for all child rows. (G) Ford (W) Mustang (W) F-150 (G)General Motors (W) Corvette (W) Silverado (G)Honda (W) S2000 (W) Ridgeline (W) Element Obviously, this involves Conditional Formatting, but how can I ask a row if it's group level or how can I band child rows to follow their parent row when the parent rows alternate background color? Thanks! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Banding groups
No real easy straightforward way to this that I can come up with. I'm not
saying there isn't, it's just that I can't think of one! See this screencap: http://img402.imageshack.us/img402/5674/cf1bq2.jpg What I did was pad the model cells with spaces. You can see the formula I used for the conditional formatting. I padded the spaces manually since there weren't that many to do. Needless to say that padding could be a SL of work if you have a lot of cells. Here's a small macro that can make it easier: Sub Pad() Dim cell As Range For Each cell In Selection If cell.Value < "" Then cell.Value = " " & cell.Value 'adjust number of spaces here Else: cell.Value = cell.Value End If Next cell End Sub This will pad each selected cell with 5 spaces. You can adjust that number by changing the commented line. You would have to clear the formatted indent then select the model cells then run the macro then apply the CF. -- Biff Microsoft Excel MVP "Habanerotary" wrote in message ... Biff, Thank you, I can make that work. I was hoping not to have to have my child rows start in column B, but I can see how my layout might have given you that impression. I've actually indented the contents of each of the columns. Is there some way to detect the level of a row within its grouping? Then identifying the parent row would make the conditional formating straightforward. Thanks so much for your response! "T. Valko" wrote: Correction: I'm assuming that the make is in one column and the models are in another column (that's how I'm interpreting the offset in your posted sample) Change the formula to: =MOD(COUNTA($A$2:$A2),2)=1 Screencap: http://img262.imageshack.us/img262/1954/cfzg7.jpg -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Try this: Assuming your data starts in cell A2. Select the range of cells in question Goto the menu FormatConditional Formatting Formula Is: =MOD(COUNTA(A$2:A2),2)=1 Click the format button Set the fill color to a shade of gray OK out -- Biff Microsoft Excel MVP "Habanerotary" wrote in message ... For readability, I would like to be able to band a spreadsheet that has been grouped. Either of the following solutions would work... Ex: Ford Mustang F-150 General Motors Corvette Silverado Honda S2000 Ridgeline Element 1. (Preferred) Alternate grey (G) and white (W) background on rows at level 1 and have all child rows inherit that background color. (G) Ford (G) Mustang (G) F-150 (W)General Motors (W) Corvette (W) Silverado (G)Honda (G) S2000 (G) Ridgeline (G) Element 2. (Acceptable) Set a grey background for all rows at level 1 and a white background for all child rows. (G) Ford (W) Mustang (W) F-150 (G)General Motors (W) Corvette (W) Silverado (G)Honda (W) S2000 (W) Ridgeline (W) Element Obviously, this involves Conditional Formatting, but how can I ask a row if it's group level or how can I band child rows to follow their parent row when the parent rows alternate background color? Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
color Banding using visual basic | Excel Worksheet Functions | |||
Colour Banding in pivot tables | Excel Discussion (Misc queries) | |||
rubber banding | Charts and Charting in Excel | |||
how do i view all groups under excel in google groups | Excel Discussion (Misc queries) | |||
Banding Results | Excel Discussion (Misc queries) |