|
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 ? -- guilbj2 ------------------------------------------------------------------------ guilbj2's Profile: http://www.excelforum.com/member.php...fo&userid=6043 View this thread: http://www.excelforum.com/showthread...hreadid=375075 |
Use this
http://www.cpearson.com/excel/banding.htm -- Regards Ron de Bruin http://www.rondebruin.nl "guilbj2" wrote in message ... 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 +-------------------------------------------------------------------+ |Filename: Book4.zip | |Download: http://www.excelforum.com/attachment.php?postid=3440 | +-------------------------------------------------------------------+ -- olasa ------------------------------------------------------------------------ olasa's Profile: http://www.excelforum.com/member.php...o&userid=17760 View this thread: http://www.excelforum.com/showthread...hreadid=375075 |
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. -- guilbj2 ------------------------------------------------------------------------ guilbj2's Profile: http://www.excelforum.com/member.php...fo&userid=6043 View this thread: http://www.excelforum.com/showthread...hreadid=375075 |
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 +-------------------------------------------------------------------+ |Filename: Book4.zip | |Download: http://www.excelforum.com/attachment.php?postid=3442 | +-------------------------------------------------------------------+ -- olasa ------------------------------------------------------------------------ olasa's Profile: http://www.excelforum.com/member.php...o&userid=17760 View this thread: http://www.excelforum.com/showthread...hreadid=375075 |
Absolutely golden, thank you so much Ola ! -- guilbj2 ------------------------------------------------------------------------ guilbj2's Profile: http://www.excelforum.com/member.php...fo&userid=6043 View this thread: http://www.excelforum.com/showthread...hreadid=375075 |
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 FormatConditional 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 |
Glad it worked Thanks for the feedback Ola Sandström -- olasa ------------------------------------------------------------------------ olasa's Profile: http://www.excelforum.com/member.php...o&userid=17760 View this thread: http://www.excelforum.com/showthread...hreadid=375075 |
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" wrote in message ... Use this http://www.cpearson.com/excel/banding.htm -- Regards Ron de Bruin http://www.rondebruin.nl "guilbj2" wrote in message ... 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 |
| All times are GMT +1. The time now is 01:13 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com