Hi, thanks...

I'm using Excel 2003.

I'm not trying to format every 3rd row (I'm trying to alternatively shade
rows that have identical values in Colum B13:B3000).

But regardless, I suppose your formula should still work and I can't figure
out why it's not. I have a feeling I may not be referencing the correct
column. Just to clarify, I changed your formula to
=MOD(SUBTOTAL(103,$B$13:$B$3000),3) but it didn't work...didn't work with the
original =MOD(SUBTOTAL(103,$A$1:$A$1000),3) either...

Anyway thanks for the suggestions... I have a feeling it's some
configuration of subtotal...



"Elkar" wrote:

If copied down through Column CA, you should get a series of:

0
2
1
0
2
1
0
2
1
Etc...

You can then apply conditional formatting to all 0's or 1's or 2's, your
preference. Thus giving the effect of every 3rd row being shaded. I think
that's what you're looking for?

If all you're getting are 0's, then perhaps you're using an older version of
Excel?


"SteveC" wrote:

Thanks... I apply that formula in CA:177 and I get zeros, instead of 0s and
1s... not sure what I'm doing wrong...

"Elkar" wrote:

If you have Excel 2003, the SUBTOTAL function will do the trick:

=MOD(SUBTOTAL(103,$A$1:$A$1000),3)

HTH,
Elkar



"SteveC" wrote:

I'm trying to create a helper column in CA to help me conditional format.

For example, the formula in CA177 is =MOD(OFFSET($BE177,-1,0)+
OR($A177<OFFSET($A177,-1,0), $B177<OFFSET($B177,-1,0)),2)

Since I'm working wtih filtered data, there are a lot of hidden rows -- I
want the formula to only apply or interact with other visible cells, so I'm
able to conditional format visible cells alternate colors.

Is there away around this? Thanks...


Value I am getting in CA
Value I want
Row 177 x Apples 1 1
Row 180 x Banannas 1 0
Row 183 x Banannas 1 0
Row 191 x Pears 1
1
Row 200 x Monkeys 1 0
Row 202 x Monkeys 1 0
Row 205 x Giraffes 1
1