Preventing a Filter from affecting cell shading

G

guilbj2

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 ?
 
O

olasa

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ö

+-------------------------------------------------------------------
|Filename: Book4.zip
|Download: http://www.excelforum.com/attachment.php?postid=3440
+-------------------------------------------------------------------
 
G

guilbj2

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

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

Debra Dalgleish

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Cell shading locked? 1
Shading a cell relative to three other cells & repeat 3
shading every other cell 2
Shading A Line Using Accent, etc. Why Other Changes Also ? 1
In VBA, How to Refer to Cell In Specific Column But Selected Rows? 7
Shading or Screening a List 1
Unwanted shading of entire spreadsheet 2
Paragraph Shading Carrying extending to Page Break Lines 5

Top