Formatting Question

[SpreadsheetMaster]

I have a spreadsheet that I have incorporated the auto filter function on. What I need to know is when I have a situation where I have filtered my data for specific information, how can I format the data without formatting the data that is hidden due to it being filtered. I have a very light grey background color selected for every other line of information to make it easier to view the data accross the page when printed for a report. But when I filter the spreadsheet it throws the background colors out of sync and this report is way too large to individually adjust each line's background color. If I copy and then "paste special" the background colors, it still does not work out because the hidden rows are formatted as well.

Thanks

[Juan Pablo González]

I'm not sure what you mean, but you may need to select only "visible" cells...

F5, Special, Visible cells (With the range selected)

[invisigirl]

When you have your info filtered like you want it, highlight the columns you want formatted, and Ctrl G (Goto). Click "Special," then select "Visible cells only." With the cells still highlighted, apply your formatting.

Hope that helps!

[sen_edp]

hello Master

when autofiltering
press f5 ->goto->special->select visible cells only

and apply the format you wish

[SpreadsheetMaster]

Thanks everyone for your replies. I appreciate it.

[SpreadsheetMaster]

I've run into a problem. When I follow the instructions above and try to paste the formats only for the selected cells I get a window that states "That command cannot be used on multiple selections".

Any ideas?

Thanks
SM

[invisigirl]

Hmm. You shouldn't have to be copying any formats. After you autofilter, select the cells you're going to want to format. All of them. THEN do the F5, Special, Visible cells only thing. Don't click off of your selected range. Go to Format, Cells, and do your formatting in there. You should be okay.

[Corticus]

Instead of worrying about sorting right, you could use conditional formatting to shade alternating rows:

=MOD(ROW()-Rw,N*2)+1<=N This is for odd banding

=MOD(ROW()-Rw,N*2)+1>=N This is for even banding

Rw = Starting Row Number

N = Number of rows in each band

(thanks to Ienze)

Corticus

[SpreadsheetMaster]

invisigirl,

Here is why I have to copy formats. The spreadsheet covers 20 or so columns and thousands of rows. I have every other row formatted with a modified background color that is a very, very light grey so that when the data is printed, the individual can easily follow the data accross the page. If also provides a nice clean look on the report. But when I autofilter the spreadsheet, the background colors get out of sequence. In other words, I end up with a couple of rows with the grey background next to each other and then the next one might be without background color, etc. So I have to copy the formats (background color) and then try to repaste over the filter data to get the correctly formatted background colors.

Corticus,

I like your formulas for conditional formatting. Will they automatically adjust for filter data or will I have to plug the formulas in after the data is filtered?

Thanks,
SpreadsheetMaster (not)