Automatically Shade Excel Rows

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 14 7/8 x 11. 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.

The rise of the laser jet heralded in the demise of the 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.

Shading_RowFunction

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.

Shading_ModFunction

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.

Shading_RibbonCommand
Shading_FormatFormula
Shading_FormatDialog

After clicking the OK button a couple of times, you should see a result similar to the following.

Shading_Every2Rows

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.

Shading_Manage
Shading_Manage2
Shading_Manage3

After clicking the OK, Apply, OK buttons, you should see a result similar to the following.

Shading_Every3Rows

Although the screen shots in this post are from Excel 2010, the procedure will be similar in Excel 2003, 2007, and 2013.

View VBA Consultants profile on LinkedIn

Copyright 2014 – VBA Consultants Ltd

Excel’s Conditional Formatting

Microsoft Excel’s Conditional Formatting allows you to create rules whereby the format of one or more cells changes based on the data in your worksheet. You would want to do this so that you can focus on the items that need review or correction. Examples could be employees with excess overtime, departments with high absenteeism or injuries, products with a high number of returns, or salesmen failing to make sales goals. Starting with the 2007 version Excel has not only made it easier to apply conditional formatting, but it also has made more formatting options available.

[Read more…]