Print Area:Can a print area automatically be defined strictly by the color of the cel

**slovenc0417** · 05-22-2007, 05:26 PM

Can a print area automatically be defined strictly by the color of the cell?

For example, if I have a large spreadsheet in which cells turn certain colors based on the data entered, can I "configure" the spreadsheets print area to automatically set to the cells that are one specific color?

**Paul** · 05-22-2007, 09:12 PM

It can't be done automatically, per se, but through vba coding you can determine the range that consists of a specific color and then set that range equal to the print area.

Are your colored cells for the print area contiguous (e.g. B2:E10 in a block), or would they be scattered cells, and you want the print area simply to encompass the top left cell of one color through the bottom right cell of that same color?

**slovenc0417** · 05-22-2007, 09:35 PM

I have attached an example of what I am hoping to accomplish. In the example I provided, I would like to print the header row, and the the rows in which the A column is shaded light blue. This is much shorter version of the spreadsheet I am dealing with. The width nor length will not vary, but the cells shaded light blue will change. That includes cells that were shaded blue eventually changing to light green. Basically the cell shaded light blue denotes an active building, which are the only ones i want to print out.

**Paul** · 05-22-2007, 10:15 PM

I think this code might work for you. Paste it into the 'ThisWorkbook' module in the vba window:

Please Login or Register  to view this content.

This code is triggered when you go to either print or print preview the sheet. It goes through every row starting with Row 2 (skipping your header row (row 1) so that won't get hidden), and if the interior colorindex is not 37 (pale blue), it will hide the entire row. In your case, it hides several rows at a time since column A uses merged cells. (For once.. merged cells made things easier!!)

After you print or print preview, the rows will remain hidden, but you can select the entire sheet and then use Format -> Rows -> Unhide to unhide all of the hidden rows.

hth

**slovenc0417** · 05-22-2007, 10:58 PM

It didn't quite work. When I went to File> Print Preview, it still showed the rows that were not light blue.

**Paul** · 05-22-2007, 11:04 PM

Are you sure you put this into your 'ThisWorkbook' section of vba? If it's anywhere else this will not work.

Also, make sure your background color is actually pale blue (colorindex=37). If it's not, you'll need to change the code to reflect the actual colorindex value.

The code works perfectly on my PC (office 2003/win xp pro).

**slovenc0417** · 05-22-2007, 11:48 PM

Oddly enough I tried it again and it worked perfectly. I'm not sure what I did the first time. Thank you very much!!! This will be very helpful.

I need to apply this to a longer spreadsheet. What part of the code do I change, and how?

Thanks again!!!

**Paul** · 05-23-2007, 12:15 AM

If only the length is changing, you shouldn't need to change the code at all. The code is already setting the iLastRow variable to the last used row in column B to find the bottom of your data. That could be row 20 or row 29291, it won't matter.

Glad I could assist.