Shade Visible Rows

Some questions...

1. Can you use AutoFiltering to hide your rows?

2. Do you have a column in your "unhidden" that
has a non-zero value on every row?

If so, you could leverage AutoFiltering,
Conditional Formatting and the SUBTOTAL worksheet
function to handle the shading.

Don,

In addition to Mark's solution, another option is to do it via a macro. Modify it for range and color (this example shades the visible cells yellow).

Tom Urtis

Sub ShadeVisible()
Application.ScreenUpdating = False
Range("A1:D20").SpecialCells(xlCellTypeVisible).Select
Selection.Interior.ColorIndex = 6
ActiveCell.Select
Application.ScreenUpdating = True
End Sub

I guess I wasn't clear on my question. I would like to shade every other row of the rows that are visible. This macro appears to shade the whole area that is visible. It's a financial statement and I want it to be easier to read across each row.
Thanks.

Re: Some questions...

I don't think I can use AutoFiltering. I'm using a program called F9 that pulls financial statement information from my accounting software. It has its own function "Zero Suppress". It looks for rows that have an account but zero amount for all columns (This Month, this month last year, year-to-date this year and year-to-date last year) then hides only those rows.

OK, then how conditional formatting?

I share a spreadsheet with two workers in other departments. Each of us accesses a large spreadsheet for different reasons, therefore we each want the data sorted a certain way for efficiency. I've never written a macro and what I want to do is program a button for each of 4 or 5 sort methods. For example, we track 3 separate order numbers, dates, Agent numbers, etc. Depending on the report we're reconciling, we want the workbook sorted a different way. Simple, but important.

How about using conditional formatting to shade every other row? Hilite your range, then click on Format > Conditional Formatting, under Condition 1 select Formula Is, and enter the formula =MOD(ROW(),2)=0 (which will shade the even numbered rows in your range). Click the Format button, choose your favorite Patterns color, and click OK.

Any help?

Tom Urtis

Yes, this is close. However, I want to shade only every other row of the visible rows. This conditional formatting makes no distinction between visible and hidden rows.

Don

See http://www.xl-logic.com/pages/addins.html #4 (NT)

Don