Before laser printers became common place (yes there was life before laser printers), people working with computer generated reports were used to green-bar paper. While the paper comes in various sizes, the most common is approximately 11x15 inches. The paper is in a carton of five thousand continuous sheets. The left and right sides of the paper have sprocket holes that align with the sprockets on an impact printer. The sprockets actually pull the paper past the print head as opposed to a laser jet's method of feeding the paper in to the print mechanism. If you pay a little extra you can get the paper with perforations by the sprocket holes so that they can be cleanly removed from the report.
It was called green-bar paper because it had alternating sections of light green coloring imprinted on it. Depending on the point size of the report, each section might contain from three to five rows of print. These alternating colored sections allow the reader of the report to 'line things up' as the reader's eyes travel across the report.
Except in certain situations, such as multi-part forms, the rise of the laser jet heralded in the demise of impact printers and with it green-bar paper. However, people still need to read landscape reports with compressed print that might squeeze the same amount of information onto a smaller page. If that is your situation, this post will allow you to mimic green-bar paper using two Excel functions along with the Conditional Formatting command.
ROW Function
The ROW function returns the row number of the current cursor location. As seen in the following screen shot, entering =ROW() in cell E2 returns 2, the row of the cursor when the formula was entered. This function by itself is not such a big deal. You could tell that the formula is in row 2 just by moving your eyes a few inches to the left. This function is the most useful when nested inside another function, or in some VBA code.
MOD Function
The MOD function returns the modulus of one number being divided by another - in plain English, the Remainder. If you enter the values and MOD function as shown in the next screen shot, the MOD function returns the value 1. Six goes into twenty-five four times with one left over.
A formula such as =MOD(ROW(),2) will return a zero whenever the row number is evenly divisible by two, i.e. an even number. Knowing this, you can shade every other row of a selected area using the Conditional Formatting command.
Select a section of the worksheet, or the entire worksheet, and then make the following choices from the Home tab of the Excel ribbon. You enter the formula in the New Format Rule dialog box and then click on the Format button to pick the fill color (i.e. shading). If you are printing on a black and white laser, very light gray will probably work best. If you are printing on a color laser, you can let your imagination run wild.
After clicking the OK button a couple of times, you should see a result similar to the following.
What if you want to change the shading to every third row, or every fifth row? You have to edit the rule for the selected cells using the following options from the same Conditional Formatting icon on the ribbon. The key is that you want to edit the rule, not delete the old rule and create a new rule. You also want to remember to change the formula to reflect how often you want the shading applied.
After clicking the OK, Apply, OK buttons, you should see a result similar to the following.
Although the screen shots in this post are from Excel 2010, the procedure will be similar in Excel 2003 and Excel 2007.
Copyright 2012 - VBA Consultants Ltd
Subscribe