Conditional formatting Question

H

HT

I am running office 2003 more specifically excel 2003. I have a project
list that I have set up conditional formatting on to set every other row to
a shaded line. My understanding is that "conditional formatting" should
hold that procedure if you sort or filter. For some reason it is not doing
so. what am I doing wrong? I have selected my entire range and under
conditional formatting specified "formula is" and for the formula I entered
"=mod(row(),2)=1" Any help would be appreciated. Thanks.

HT
 
D

David McRitchie

Hi .....,
If you have every cell used in a particular column (col A) with or without
filtering then you can use Conditional Formatting like this
=MOD(SUBTOTAL(2,$A$1:$A1),2)=0 every other row
select any cell on row 1, then use Ctrl+Shift+SpaceBar to select all rows
before entering your condition formatting formula.

which might look a bit busy, you might prefere
=MOD(SUBTOTAL(3,$A$1:$A1),3)=0

More on Conditional Formatting not necessarily related to your question
http://www.mvps.org/dmcritchie/excel/condfmt.htm
http://www.mvps.org/dmcritchie/excel/sumdata.htm
 
G

Gord Dibben

HT

Your understanding is correct. The rows should stay shaded through sorting or
filtering.

My 2003 has no problem with this.

What is happening instead of the alternate row shading?


Gord Dibben Excel MVP
 
R

Roger Govier

Hi
It works fine for me on 2003.
Are you sure it is not behaving correctly?
Take a look at the row numbers of your filtered data they are probably not
all odd numbers, as the filter has selected some even rows, possibly in
succession.

Regards

Roger Govier
 
H

HT

When I do the filter, It basically carried the shading with the row. so
what I will get when I run the filter is 2-3 rows of shaded then 3-4 rows of
white etc just based on what the row started out as. Does every cell in a
row need to be filled out in order for the formatting to work?
 
R

Roger Govier

The shading is carried with the row, because that's what you told it to do.
You selected uneven rows to be shaded, that's what CF is following.

The criteria on which you are filtering, is selecting rows according to
their values, independent of row numbers and obviously has ranges of
adjacent even rows and odd rows occurring in blocks.


Regards

Roger Govier
 
D

David McRitchie

I provided a reply in this thread for filtered rows. If you do not
have all of the cells used in the column used for testing then
it will not work. But there is no way that your formula as you
supplied it would work on a filtered list if you randomly filtered
out rows.
 
H

HT

David,

Thanks for the info and the help I hadn't tried the formulas before my
reply. It worked great. Thanks again.


David McRitchie said:
I provided a reply in this thread for filtered rows. If you do not
have all of the cells used in the column used for testing then
it will not work. But there is no way that your formula as you
supplied it would work on a filtered list if you randomly filtered
out rows.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

HT said:
When I do the filter, It basically carried the shading with the row. so
what I will get when I run the filter is 2-3 rows of shaded then 3-4 rows
of
white etc just based on what the row started out as. Does every cell in
a
row need to be filled out in order for the formatting to work?
Click to expand...
Click to expand...
 
Top