Greenbar Effect

The site http://www.cpearson.com/excel/banding.htm has what you're looking for.

JAF

JAF,

I was afraid you'd say that (or at least the site would say that)!

My sheet already contains conditional formatting.

I'd lose what I already have to implement that scheme.

Further, some rows are hidden which throws the coloring off as even (or odd) rows could be adjacent.

What I need is a macro which sets the effect on visible rows only.

This macro would only need to be run, say prior to printing.

Actually, it would be nice to be event driven in that the macro would run when the call to printing was executed.

Don't get me wrong.

I do a similar process on visible rows using formulas to assign a TRUE value to every other row.

Then I filter the TRUE values and color those visible rows.

Next, I unfilter and WHAM!! Greenbar!!

I suppose I could write a macro to automate that process but would MUCH prefer custom VBA which test each row for visibility and than accordingly colors those seen.

Can you test whether a row is visible?

Bill
Firstly, with your recently acquired expertise, you can use the macro recorder to get the code for selecting visible cells only.
Thereafter, just add code to color every other row in the selection green.
This is one way :-

Dim Cell As Range, n As Integer
n = 0
For Each Cell In Selection
Cell.Activate
n = n + 1
If n Mod 2 = 0 Then
ActiveCell.EntireRow.Interior.ColorIndex = 35
End If
Next
End Sub

Celia

Ahhhh,

You are referring to GOTO under the EDIT menu?

I acquired this:

Selection.SpecialCells(xlCellTypeVisible).Select

Will see if this works...

Thank you!

Celia,

Your code is great.

Of course, it could bomb out past row 32767 ;)

Kidding aside, What is the impact of this:

Does this explicitly become the active range?

.....Dim Cell As Range

Also, does the Array CELL provide absolute or relative referencing?

bill

Bill
Not sure I know how to answer, but :- Of course, it could bomb out past row 32767 ;)
But not if you are using versions later than Excel 95 ! Kidding aside, What is the impact of this:
Cell (or any other word you want to use) is merely the variable which provides the vehicle for looping through the selected range.

Celia

PS. The code would be better like this :-

Dim Cell As Range, n As Integer
n = 0
For Each Cell In Selection
n = n + 1
If n Mod 2 = 0 Then
Cell.EntireRow.Interior.ColorIndex = 35
End If
Next

Celia,

Perhaps your version of EXCEL is configured differently than mine...

"n" is defined as integer in the:

Dim Cell As Range, n As Integer

statement. As an Integer, "n" is limited to the range of -32,768 to 32,767.

Therefore, values referring to a row greater than 32,767 would cause an error.

To get around this, "n" should be dimensioned as LONG which provides a range of -2,147,483,648 to 2,147,483,647.

This consumes 4 bytes of storage memory.

The other alternative is to define it as VARIANT (or skip declaration which causes VBA to default to VARIANT anyway).

"n"'s range now is:
4.94065645841247E-324 to 1.79769313486232E308
for all positive values.

This is essentially the definition for DOUBLE but consumes 16 bytes of storage memory!

I guess the other thing you can do is prevent "n" from exceeding 32,767...

In ACCESS, I've forced myself to require that all variables be defined prior to their usage.

This can be done also in EXCEL (VBA) by using the code:

Option Explicit

at the beginning of each mudule.
_____________________________________

As I force myself to declare variable in EXCEL prior to its use, I in fact suffered this error recently and was forced to change to LONG.

I am using EXCEL 97.

Bill

Bill
But there again, it is claimed by many people that length is not necessarily important. Particularly by those who have a small macro.
celia