MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Greenbar Effect


Posted by bill.roberts on March 21, 2000 12:59 PM

People,

Thought I saw a thread on the BBS regards VBA greenboard effect.

I searched for COLOR, EVERY, OTHER, GREEN, BAR, MAKE but came up with nothing.

Perhaps it has been acrhived?


Posted by JAF on March 22, 2000 2:49 AM


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

JAF

Posted by bill.roberts on March 22, 2000 8:53 AM

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?

Posted by Celia on March 23, 2000 4:54 AM

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


Posted by bill.roberts on March 23, 2000 11:25 AM


Ahhhh,

You are referring to GOTO under the EDIT menu?

I acquired this:

Selection.SpecialCells(xlCellTypeVisible).Select

Will see if this works...

Thank you!

Posted by bill.roberts on March 24, 2000 7:13 AM


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

Posted by Celia on March 24, 2000 12:59 PM

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


Posted by Celia on March 24, 2000 1:46 PM

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

Posted by bill.roberts on March 27, 2000 9:15 AM


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

Posted by Celia on March 27, 2000 3:19 PM

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