Color Banding

[Paul Axelrod]

I have read the material at the Chip Pearson's website on color
banding in a worksheet to emulate greenbar. Works great, but I dont
believe I understand what precisly the Conditional formatting formula
is doing.
I have used both
=MOD(ROW()-Rw,N*2)+1<=N Rw is the starting row number to be banded,
N is the number of rows to be banded.
and
=mod(ROW(),2)
Can someone please take a few minutes to explain.
Thank you.

 

[Frank Kabel]

Hi Paul
MOD(ROW(),2)
returns the rest of the divison of the row number and two. e.g.
row 1: MOD(1,2) = 1
row 2: MOD(2,2) = 0
row 3: MOD(3,2) = 1
row 4: MOD(4,2) = 0

Excel evaluates 0 as FALSE and 1 as TRUE so this formula either returns
TRUE or FALSE for a row number.

 

[Norman Harker]

Hi Paul!

=MOD(ROW(),2)

Here you have the formula for color banding every other row.

MOD returns the modulus (or remainder) of the number (first argument)
divided by the second argument.
ROW() returns the row number of the reference that is usually given as
an argument. But where the argument is omitted, the function assumes
you want the row number of the cell the function is in.

The formula therefore returns 1 or 0. If it is 0, this is treated as
FALSE and the conditional format is not applied. If it is 1, then it
is treated as TRUE and the conditional format is applied.


=MOD(ROW()-Rw,N*2)+1<=N

Exactly the same logic is being applied here only in this case we are
looking for a result of banding in groups of N rows. To see how the
formula works, I suggest that you insert the following formula in A1:

=MOD(ROW()-1,3*2)+1
Copy down (say) 12 rows

Now in B1 use:

=A1<=3
Copy down as before.

You'll see now that if we take 1 from the Row number and divide by
twice the pattern (3*2) wanted and add 1, we will get the series of
modulus 1,2,3,4,5,6. If (as in column B) we look for column A being
less than or equal to the pattern (3) we will get pattern rows TRUE
followed by pattern rows FALSE.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[David McRitchie]

It's all there in both Frank's and Norman's replies but

Just to reiterate:
The idea behind Conditional Formatting and filters for that matter
is to test for a condition, which will provide either TRUE or FALSE.

More information on Conditional Formatting in
http://www.mvps.org/dmcritchie/excel/condfmt.htm

 

[Frank Kabel]

Hi David
good addition. Missed the basic for all this in my explanation

 

[Paul Axelrod]

Thank you all. It has suddenly become crystal clear.
You guys do a great service for all us ordinary users.