Solved: Color banding

[justdream]

Dears,


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

[Bob Phillips]

I have a web page on this topic

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

[justdream]

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

[Bob Phillips]

You could maybe use

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

[mikerickson]

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]

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]

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]

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.

[VBA]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
[/VBA]