Clear the Input Cell Style for Printing

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.

Objectives

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.

Input cells by Jeff Lenning

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.

Input Style by Jeff Lenning

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.

Details

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:

  1. Clear the Input Style Formats
  2. Print the Worksheet
  3. 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!

Additional Resources

 

 

Posted in , ,

Jeff Lenning

I love sharing the things I've learned about Excel, and I built Excel University to help me do that. My motto is: Learn Excel. Work Faster.

Excel is not what it used to be.

You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.

Want to learn Excel?

Our training programs start at $29 and will help you learn Excel quickly.

10 Comments

  1. Dave Marriott on February 17, 2017 at 11:56 am

    You can achieve a similar result by using the black and white checkbox in page setup.

    • Jeff Lenning on February 17, 2017 at 12:04 pm

      Thanks Dave!

  2. Steven Winokur on February 20, 2017 at 10:50 am

    OK… I have to admit I am a super high end Excel user who can not master macros. SO I don’t even think in that language (though I did big time in the old Lotus days). Anyway… I have a different solution completely:

    A) I also love to have an input worksheet that feeds my real worksheet.
    B) I always protect myself by setting up my worksheets with conditional formatting that tells me if someone inadvertently puts a number in a formula cell by using “+not(isformula(a1))” and having the result print in a different color. BUT, I don’t want that to show when I print.
    C) So I set up a cell in my input sheet that uses data validation and only gives the choice of “hide” or “don’t hide”
    D) Then in my real worksheet, I set up a final conditional format that says if that cell says “hide” then have no highlighting.
    E) Poof, highlighting is gone. It’s easy to toggle on and off for review purposes as well.

    • Jeff Lenning on February 20, 2017 at 10:52 am

      Great idea! Thanks for sharing it 🙂

  3. John Abrassart on February 23, 2017 at 10:11 am

    I concur completely. Having an Input “style” that’s applied to input areas of your sheet is invaluable. And, I agree with Steve that having an INPUT tab in each workbook I build is pretty common. My Input Style only affects two things: the Fill and the Protection (No protection). The latter setting limits users to just entering data in your input cells if you decide to Protect your workbook.

    I usually select light cyan, light green or light yellow for my Fill because it still provides the visual cue and is virtually undetectable on a lot of printers. Although, we don’t actually mind printing the colors in most cases. But if I have a situation in which I do want to turn the color off, I just modify the Input Style to “No Fill” temporarily, print the sheet and then return the Input Style to its original setting.

    I suppose I could do this with a macro but I prefer to keep my sheets simple.

    • Jeff Lenning on February 23, 2017 at 10:12 am

      Thanks for your comment…really appreciate it!

  4. M on April 22, 2017 at 2:28 am

    I am getting into the habit of having an “instructions_settings” tab on each of my regular spreadsheets, then hide the tab when I send it to head office. This tab has the dates and other input details plus quick screen shots and instructions. Some spreadsheets I only complete on a quarterly basis so I sort of forget the best way to complete them, these instructions just refresh my memory.

    I love the macro idea though.

    I love your blog. Thank you.

    • Jeff Lenning on April 22, 2017 at 4:10 am

      Excellent ideas, and thanks 🙂

  5. sandeep kothari on July 29, 2018 at 5:32 am

    XLent!

  6. Ben on November 25, 2019 at 12:01 am

    Not sure if this is the right place to ask but I’ll try anyway. I know how to use “cell styles” however, what I’m trying to do is use a cell on my spreadsheet that I can click that does the same thing as clicking a “cell style” in the upper menu bar. any help would be greatly appreciated.

Leave a Comment