PDA

View Full Version : Solved: Color banding



justdream
12-18-2010, 12:58 PM
Dears,


Please help to create Macro to color only:
- Specific column (E for example)
- for each group of rows (8 for example)

Bob Phillips
12-18-2010, 01:18 PM
I have a web page on this topic

http://xldynamic.com/source/xld.CF.html#rows

justdream
12-18-2010, 01:31 PM
Thanks a lot, I've read it almostly
I don't want for example: to stripe (every/ each) 3rd row, no I'd like to stripe (each 3 row's' together)

How could I do that :think:

Bob Phillips
12-18-2010, 03:15 PM
You could maybe use

=MOD(INT((ROW()+(num_per_group-1)/num_per_group),2)

mikerickson
12-19-2010, 01:30 AM
You could use CF with a general equation:

=(MOD((ROW()-offset_index)/batch_size,repeat_value)<count _ of highlighted_batches)

Where batch_size is the number of rows in a batch.
repeat_value is how many batches before the pattern repeats
count _ of highlighted_batches is the number of batches to hightlight

=(MOD((ROW()-1)/ 3, 2) < 1)
will hightlight 3 rows at a time (batch_size) every other three rows (repeat_value)
so that 1:3 are highlighted, 4:6 not, 7:9 highlight

=(MOD((ROW()-1)/ 3, 3) < 1) gives
1:3 highlighted 4:9 not 10:12 highlight 13:18 not
batch_size is 3, repeat_value is 3, so the highliting repeates every third batch with the pattern Yes No No Yes No No

=(MOD((ROW()-1)/ 3, 3) < 2) gives makes the count _ of highlighted_batches 2, so the 3,3 pattern is

Yes Yes No Yes Yes No or
1:6 highlight 7:9 not 10:15 highlighted 16:18 not

offset_index determines which is the first row to be highlited.

justdream
12-19-2010, 12:24 PM
You could use CF with a general equation:

=(MOD((ROW()-offset_index)/batch_size,repeat_value)<count _ of highlighted_batches)

Where batch_size is the number of rows in a batch.
repeat_value is how many batches before the pattern repeats
count _ of highlighted_batches is the number of batches to hightlight

=(MOD((ROW()-1)/ 3, 2) < 1)
will hightlight 3 rows at a time (batch_size) every other three rows (repeat_value)
so that 1:3 are highlighted, 4:6 not, 7:9 highlight

=(MOD((ROW()-1)/ 3, 3) < 1) gives
1:3 highlighted 4:9 not 10:12 highlight 13:18 not
batch_size is 3, repeat_value is 3, so the highliting repeates every third batch with the pattern Yes No No Yes No No

=(MOD((ROW()-1)/ 3, 3) < 2) gives makes the count _ of highlighted_batches 2, so the 3,3 pattern is

Yes Yes No Yes Yes No or
1:6 highlight 7:9 not 10:15 highlighted 16:18 not

offset_index determines which is the first row to be highlited.

Dear Friend, you have made my day (I was searching for that since 10 days)

justdream
12-19-2010, 01:04 PM
Another favour please,
I'm trying to record it in macro, so I can have it automatically for my daily worksheets but alawys Macro record blank data...

mikerickson
12-19-2010, 01:17 PM
This is what came off the macro recorder for me (plus clean-up).
There is one line that is no applicable to pre-2007 versions.

Sub Macro1()

With Range("E6:F19")

.FormatConditions.Add Type:=xlExpression, Formula1:="=(MOD((ROW()-1)/ 3, 2) < 1)"

.FormatConditions(.FormatConditions.Count).SetFirstPriority: Rem 2007 and later only

With .FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ColorIndex = 6
End With

End With
End Sub