Preventing a Filter from affecting cell shading

I've shaded every other line on a spreadsheet to make it easier to read, but I'm also using filters. When I filter results, the cell shading is also filtered out, so instead of white line/gray line over and over I get blocks of colour which defeat the purpose of the shading. Does anyone know a way around this ?

Use this
http://www.cpearson.com/excel/banding.htm

--
Regards Ron de Bruin
http://www.rondebruin.nl



"guilbj2" <[email protected]> wrote in message
news:[email protected]...
>
> I've shaded every other line on a spreadsheet to make it easier to read,
> but I'm also using filters. When I filter results, the cell shading is
> also filtered out, so instead of white line/gray line over and over I
> get blocks of colour which defeat the purpose of the shading. Does
> anyone know a way around this ?
>
>
> --
> guilbj2
> ------------------------------------------------------------------------
> guilbj2's Profile: http://www.excelforum.com/member.php...fo&userid=6043
> View this thread: http://www.excelforum.com/showthread...hreadid=375075
>

This is a beginning (Conditional Formatting)

Blue: =MOD(SUBTOTAL(3;B$6:B4);2)=0
Green: =MOD(SUBTOTAL(3;B$6:B4);2)=1

Ola Sandström

Thanks for the help guys, but I'm a total newbie to conditional formatting... The range I need to get setup are D8:D34. Could you elaborate on how/where I should enter these formulas ?

Just to be a royal pain, I'm trying to alternate between white and light green as well if you know the correct colour codes.

Sure.
Here is video that shows how to use Conditional Formatting:
http://www.datapigtechnologies.com/f...rmatexcel.html

Also I've enclosed an update that should work just a little bit better.

Hope it helps
Ola Sandström

Absolutely golden, thank you so much Ola !

Glad it worked
Thanks for the feedback
Ola Sandström

You can do this if you add a helper column to the table:

Insert a blank column (column A in this example)
Add a heading, e.g. Count
In row 2, enter the following formula, where column B
contains no blank cells within the table range:
=SUBTOTAL(3,$B$2:$B2)
Copy the formula down to all rows of data
Select cell A1
Select all the cells on the worksheet (Ctrl + A)
Choose Format>Conditional Formatting
From the first dropdown, choose Formula Is
In the formula box, type:
=AND($A1<>"",MOD($A1,2)=0)
Click the Format button, and select a colour for the shaded rows
Click OK, click OK

Filter the table, the count will change, and alternate rows will be shaded.

guilbj2 wrote:
> I've shaded every other line on a spreadsheet to make it easier to read,
> but I'm also using filters. When I filter results, the cell shading is
> also filtered out, so instead of white line/gray line over and over I
> get blocks of colour which defeat the purpose of the shading. Does
> anyone know a way around this ?
>
>


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

After reading Debra's reply I see that I give you a wrong answer.
You say Filter and not Sort

--
Regards Ron de Bruin
http://www.rondebruin.nl



"Ron de Bruin" <[email protected]> wrote in message news:%[email protected]...
> Use this
> http://www.cpearson.com/excel/banding.htm
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
>
> "guilbj2" <[email protected]> wrote in message
> news:[email protected]...
>>
>> I've shaded every other line on a spreadsheet to make it easier to read,
>> but I'm also using filters. When I filter results, the cell shading is
>> also filtered out, so instead of white line/gray line over and over I
>> get blocks of colour which defeat the purpose of the shading. Does
>> anyone know a way around this ?
>>
>>
>> --
>> guilbj2
>> ------------------------------------------------------------------------
>> guilbj2's Profile: http://www.excelforum.com/member.php...fo&userid=6043
>> View this thread: http://www.excelforum.com/showthread...hreadid=375075
>>
>
>