I really like to highlight input cells by applying the Input Cell Style. I also like to try to place all of the input cells for a workbook on a single worksheet. That way, it is easy to update the input cells, and since they aren’t on any of the sheets I need to print, the input cell style formatting isn’t an issue. However, sometimes it may be necessary to place an input cell in a worksheet that will be printed. So, this post provides a macro that instantly removes the Input Cell Style formatting so you can print. A second macro restores the default cell style formatting.
Before we jump right in to the technical stuff and the VBA code, let’s just be sure we are on the same page. Let’s say we have some input cells that store the company name and contact name, as shown below.
We want to identify the input cells to make it easy for a user to know which cells require input. So, we select the input cells and apply the Input Cell Style (found in the Cell Styles gallery on the Home tab). The result is shown below.
Now the input cells are easy to see. When we apply the Input Cell Style on worksheets that don’t need to be printed, life is good. But, when we apply the Input Cell Style on sheets that are designed to be printed, we may not want the formatting to appear on the printed page.
So, this post walks through one way to approach this issue, which is to run a macro prior to printing that clears the Input Cell Style formatting. Then, you would print the worksheet. Then, you would run another macro that restores the Input Cell Style formatting.
Sound good? Alright, let’s get to it.
Since we have previously discussed recording macros, the personal macro workbook, and setting up a macro toolbox, rather than rehash all of those details again here, I’ll provide links to those posts below, and proceed directly to the VBA code. You can copy the code and paste it into your VB Editor as needed. Or, if you prefer, just download the sample Excel file below which has the VBA code.
In practice, after applying the Input Cell Style, the basic idea is that you would:
- Clear the Input Style Formats
- Print the Worksheet
- Restore the Input Style Formats
Let’s walk through these steps.
Clear the Input Style Formats
So, assuming that you have already applied the Input Cell Style to various cells throughout the workbook, you’ll want to run this macro.
Sub InputStyleClear() With ActiveWorkbook.Styles("Input") .Interior.Pattern = xlNone .Font.ColorIndex = xlAutomatic .Borders(xlLeft).LineStyle = xlNone .Borders(xlRight).LineStyle = xlNone .Borders(xlTop).LineStyle = xlNone .Borders(xlBottom).LineStyle = xlNone End With End Sub
As you look through the code, you can see it applies various formats to the Input style, namely, it removes the fill, sets the font color to automatic, and removes the borders.
Note: There are a variety of ways to run macros, including setting up a QAT button, a worksheet button, the Macros dialog, or even the VB editor. For more information, check out the additional resources below.
After you run the macro, the Input Cell Style is still applied to the input cells, but, the formatting is updated so that it appears as any normal cell, that is, without the fill, borders, or font color.
Print the Worksheet
Now, you can print the worksheet as needed, and the input cells won’t display the typical blue borders, blue font, and cell fill.
Once you are done printing, you are ready to restore the formats.
Restore the Input Style Formats
To restore the default Input Style formats, you would run this macro.
Sub InputStyleRestore() With ActiveWorkbook.Styles("Input") .Interior.Color = 10079487 .Font.Color = -9027777 .Borders(xlLeft).Color = -8421505 .Borders(xlRight).Color = -8421505 .Borders(xlTop).Color = -8421505 .Borders(xlBottom).Color = -8421505 End With End Sub
As you look through the code, you can see that it updates the Input Style format, and essentially restores the built-in formatting.
If you have any other Style-related tips, please share by posting a comment below…thanks!
- Excel macro-enabled file (zipped): InputCellStyle.zip
- A post that talks about recording macros and setting up a QAT button: Accounting Number Formats QAT
- A post that talks about storing macros: Macro Toolbox