MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Shade Visible Rows

Posted by Don Ressler on November 02, 2001 7:20 AM

I have a worksheet that I hide certain rows that I do not want to print. Then I would like to shade alternate VISIBLE rows. I know how to do alternate shading using conditional formatting but not on visible rows only.

Posted by Mark W. on November 02, 2001 7:40 AM

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.

Posted by Tom Urtis on November 02, 2001 7:50 AM


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
Selection.Interior.ColorIndex = 6
Application.ScreenUpdating = True
End Sub

Posted by Don Ressler on November 02, 2001 9:30 AM

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.

Posted by Don Ressler on November 02, 2001 9:37 AM

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.

Posted by Tom Urtis on November 02, 2001 9:55 AM

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.

Posted by Take 2 ( Halloween script gremlins at work): Then how about conditional formatting? on November 02, 2001 10:07 AM

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

Posted by Don Ressler on November 02, 2001 10:21 AM

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.


Posted by Juan Pablo on November 02, 2001 11:13 AM

See #4 (NT)