Conditional Formating - Different row color also when using filter
 

Hi

Can anyone give a hint on how to make each 2'nd line colored - also when
using filtering. I am currently using conditional format formular:
mod(row();2)=0, but when using filter it happens that nestet rows has all odd
or even numbers, resulting in the same color.

Any suggestions to solve this issue?

/Steen

Conditional Formating - Different row color also when using filter
 

Check out this excellent website:
http://www.xldynamic.com/source/xld.CF.html#rows I think you'll find what you
need there.


"Steen" wrote:

Hi

Can anyone give a hint on how to make each 2'nd line colored - also when
using filtering. I am currently using conditional format formular:
mod(row();2)=0, but when using filter it happens that nestet rows has all odd
or even numbers, resulting in the same color.

Any suggestions to solve this issue?

/Steen

Conditional Formating - Different row color also when using filter
 

Try this conditional format formula, taken from a post by Tom Ogilvy,
http://tinyurl.com/dsu87

" If the hidden rows are cause by applying a filter
then this formula will work:

=MOD(SUBTOTAL(3,$A$1:A1),2)=0

Select the rows to format (this assumes the formatting starts with row 1)
Enter the formula as if it were for the the currently active cell (in this
case A1). The range must be applied to a column that will contain values in
each visible cell in the column. "

The formula above will also take care of the normal unfiltered situation

(Adapt to suit your continental version: semicolon separators instead of
commas)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Steen" wrote:
Can anyone give a hint on how to make each 2'nd line colored - also when
using filtering. I am currently using conditional format formular:
mod(row();2)=0, but when using filter it happens that next rows has all odd
or even numbers, resulting in the same color.

Any suggestions to solve this issue?

/Steen

Conditional Formating - Different row color also when using fi
 

Hmm, this almost works but not quite. I'm trying to do the same thing, so I
tried it, but it's also coloring every other column.

For example, I had to substitute $A:$6:A6 in the formula and now see A6, C6,
F6, and G6 as white, but B6, D6, F6, and H6 are gray.
Row 7 shows the correct gray color across A:H.
Then row 8 repeats the behavior from row 6.
This is working as advertised with a filtered list, but any ideas why it's
making columns B, D, F, and H solid gray??

--
Please remember to indicate when the post is answered so others can benefit
from it later.


"Max" wrote:

Try this conditional format formula, taken from a post by Tom Ogilvy,
http://tinyurl.com/dsu87

" If the hidden rows are cause by applying a filter
then this formula will work:

=MOD(SUBTOTAL(3,$A$1:A1),2)=0

Select the rows to format (this assumes the formatting starts with row 1)
Enter the formula as if it were for the the currently active cell (in this
case A1). The range must be applied to a column that will contain values in
each visible cell in the column. "

The formula above will also take care of the normal unfiltered situation

(Adapt to suit your continental version: semicolon separators instead of
commas)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Steen" wrote:
Can anyone give a hint on how to make each 2'nd line colored - also when
using filtering. I am currently using conditional format formular:
mod(row();2)=0, but when using filter it happens that next rows has all odd
or even numbers, resulting in the same color.

Any suggestions to solve this issue?

/Steen

Conditional Formating - Different row color also when using fi
 

I just figured it out. Simply put a dollar sign in front of the second A.
Instead of
=MOD(SUBTOTAL(3,$A$1:A1),2)=0
use
=MOD(SUBTOTAL(3,$A$1:$A1),2)=0

All we really care about is the counta from column A, so that can stay fixed.
YES!!

- KC Rippstein
--
Please remember to indicate when the post is answered so others can benefit
from it later.


"KC Rippstein" wrote:

Hmm, this almost works but not quite. I'm trying to do the same thing, so I
tried it, but it's also coloring every other column.

For example, I had to substitute $A:$6:A6 in the formula and now see A6, C6,
F6, and G6 as white, but B6, D6, F6, and H6 are gray.
Row 7 shows the correct gray color across A:H.
Then row 8 repeats the behavior from row 6.
This is working as advertised with a filtered list, but any ideas why it's
making columns B, D, F, and H solid gray??

--
Please remember to indicate when the post is answered so others can benefit
from it later.


"Max" wrote:

Try this conditional format formula, taken from a post by Tom Ogilvy,
http://tinyurl.com/dsu87

" If the hidden rows are cause by applying a filter
then this formula will work:

=MOD(SUBTOTAL(3,$A$1:A1),2)=0

Select the rows to format (this assumes the formatting starts with row 1)
Enter the formula as if it were for the the currently active cell (in this
case A1). The range must be applied to a column that will contain values in
each visible cell in the column. "

The formula above will also take care of the normal unfiltered situation

(Adapt to suit your continental version: semicolon separators instead of
commas)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Steen" wrote:
Can anyone give a hint on how to make each 2'nd line colored - also when
using filtering. I am currently using conditional format formular:
mod(row();2)=0, but when using filter it happens that next rows has all odd
or even numbers, resulting in the same color.

Any suggestions to solve this issue?

/Steen

Conditional Formating - Different row color also when using fi
 

Good to hear that ! Thanks for posting your findings.

Yes, like in life itself, those dollar signs can really make a crucial
difference <g
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"KC Rippstein hotmail com" <kcrippstein<atdot wrote in message
...
I just figured it out. Simply put a dollar sign in front of the second A.
Instead of
=MOD(SUBTOTAL(3,$A$1:A1),2)=0
use
=MOD(SUBTOTAL(3,$A$1:$A1),2)=0

All we really care about is the counta from column A, so that can stay
fixed.
YES!!

- KC Rippstein