LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 691
Default Banding - visible rows only?

As I mentioned, random rows (dependent on user filtering) are hidden
by the code: Rows(ActiveCell.Row).Hidden = True


That is not filtering, and you did not mention it quite that way
before. What have are hidden rows (basically zero height).

You would first have the macro remove the interior color from
all rows then since you would not want the top row colored, you
can start from the top and work on the non hidden rows.

Option Explicit
Sub Band_alternate_35()
'David McRitchie, programming, 2004-01-12
'Color Band alternate rows not hidden by user
'-- as in .... Rows(ActiveCell.Row).Hidden = True
'-- not for use on filtered rows
'almost 5 minutes to do 20000 rows
Dim i As Long, nothidden As Boolean
i = ActiveSheet.UsedRange.Rows.Count '-- fix usedrange
Cells.Interior.ColorIndex = xlNone
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For i = 1 To Cells.SpecialCells(xlLastCell).Row
If Not Rows(i).hidden Then
nothidden = nothidden + 1
If Not nothidden Then Rows(i).Interior.ColorIndex = 35
End If
Next i
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"NorTor" wrote in message ...
As I mentioned, random rows (dependent on user filtering) are hidden
by the code: Rows(ActiveCell.Row).Hidden = True

And I want the banding to count only the rows that are visible after
the user has done the filtering, in a way that every second row is
colored and the ones in between are un-colored.



 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Use VBA to shade every 3 rows (banding) MikeS Excel Discussion (Misc queries) 5 June 2nd 09 01:27 PM
Deleting Visible Rows Confused Excel Discussion (Misc queries) 4 August 7th 08 02:44 PM
Visible Rows Indexing John Wilson Excel Programming 7 November 2nd 03 02:54 PM
List Box Visible Rows Only John Wilson Excel Programming 5 October 28th 03 07:50 PM
AutoFilter - which rows are currently visible? Bjørnar Hartviksen Excel Programming 2 October 6th 03 12:01 AM


All times are GMT +1. The time now is 04:59 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"