Banding groups

[Guest]

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!

 

[T. Valko]

Try this:

Assuming your data starts in cell A2.

Select the range of cells in question
Goto the menu Format>Conditional 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

 

[T. Valko]

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

 

[Chip Pearson]

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)

 

[Guest]

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]

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.