How to shade rows with three different colours

[Aussiebear]

Having followed a link kindly offered by Pascal in the thread "Banding formats", the section headed How to shade rows with three different colours, has left me somewhat confused, and unfortunately I am some days away from testing the following concepts within an excel sheet.

To highlight 1st and 3rd rows use Mod(Row($A2)+3-1,3)=1.
To highlight 2nd, 6th, 9th etc use Mod(Row($A2)+3-1,3)=2.
To highlight 3rd, 7th, 10th etc use Mod(Row($A2)+3-1,3)=0.


The only value that differs between the formulas is the end value ( 1,2,0) yet the requirements are significantly different. What is it that tells Excel the band value?

Secondly, given that the starting cel is always $A2, the +3-1 does what?

[Bob Phillips]

It is just MODding the row number with 3, 3 because the banding is in 3 parts. As help says, MOD returns the remainder after number is divided by divisor, so MOD(1,3) returns 1, MOD(2,3) returns 3, and MOD(3,3) returns 0. So you just test those values to determine which colour each row gets.

I just don't understand the +3-1 part. I can see that it is adding two to each row number but why? It changes the value that you test against, but it isn't required to make it work or make it simpler, I would just use

=MOD(ROW($A2),3)=2
=MOD(ROW($A2),3)=1
=MOD(ROW($A2),3)=0

[Bob Phillips]

BTW, I cover this in my page on CF http://www.xldynamic.com/source/xld.CF.html#rows

[Aussiebear]

Thanks Bob. The other issue here is that the requirement was to test 2nd, 6th and 9th for the second, and 3rd, 7th and 10th. The pattern is jump 4 rows then 3. Shouldn't we be seeing 2nd, 5th and 8th?

[Bob Phillips]

I thought it was to set the colour on a change of value, so one group (however many rows) was one colour, the next group (again, however many) another colour., For that, a helper column is so much simpler, and CF based upon that column.

[Aussiebear]

That was the intent in the other thread, but I am referring to the information as posted in the link provided by Pascal. However it doesn't matter as I shall regard it as incorrect.

[snb]

The suggestions in that link are unnecessary complicated.
You could suffice with: see the attachment

[Paul_Hossler]

BTW, I cover this in my page on CF http://www.xldynamic.com/source/xld.CF.html#rows

1. Thanks for the link, and I bookmarked the home page. I never bumped up against it Googling for answers/tips

2. Have you ever considered adding it to your signature?

[Paul_Hossler]

I was also confused by the +3-1 but assumed that it has to do with the link banding in groups of multiple rows


http://www.ablebits.com/office-addin...l/#value-based

How to alternate groups of rows with different colorsYou can use the following formulas to shade a fixed number of rows, regardless of their content:
Odd row shading, i.e. highlight the 1^st group and every other group:
=MOD(ROW()-RowNum,N*2)+1<=N
Even row shading, i.e. highlight the 2nd group and all even groups:
=MOD(ROW()-RowNum,N*2)>=N
Where RowNum is a reference to your first cell with data and N is the number of rows in each banded group.

Are you looking for the entire row to be shaded in (e.g.) Red-Yellow-Green-Red-Yellow-Green-etc. order for row 1, 2, 3 --- 4, 5,6 ---- etc.

[Aussiebear]

Sorry if I've mislead everyone here, but I simply wanted to clarify the some of the content within the link that Pascal had kindly provided.