Shade a cell but not print that color?

%3CLINGO-SUB%20id%3D%22lingo-sub-93136%22%20slang%3D%22en-US%22%3EShade%20a%20cell%20but%20not%20print%20that%20color%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-93136%22%20slang%3D%22en-US%22%3E%3CP%3EIs%20there%20a%20way%20I%20can%20shade%20a%20cell%2C%20but%20keep%20that%20cell%20from%20being%20shaded%20when%20I%20print%20the%20sheet%3F%20For%20example%2C%20I%20want%20someone%20to%20enter%20data%20only%20in%20the%20cells%20I've%20shaded%20yellow%2C%20but%20I%20want%20the%20finished%20print%20to%20not%20have%20yellow%2C%20shaded%20cells.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-93136%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-171410%22%20slang%3D%22en-US%22%3ERe%3A%20Shade%20a%20cell%20but%20not%20print%20that%20color%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-171410%22%20slang%3D%22en-US%22%3E%3CP%3EThis%20is%20the%20solution.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-133350%22%20slang%3D%22en-US%22%3ERe%3A%20Shade%20a%20cell%20but%20not%20print%20that%20color%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-133350%22%20slang%3D%22en-US%22%3E%3CP%3EJoel%2C%26nbsp%3Btry%26nbsp%3Busing%20conditional%20formatting%20(Home%2C%20Styles%2C%20Conditional%20Formatting).%20Select%20the%20cells%20you%20want%20to%20be%20highlighted%20yellow%20and%20click%20Conditional%20Formatting%2C%20New%20Rule.%20Under%20Rule%20Type%20select%20%22Format%20only%20cells%20that%20contain%22%20and%20choose%20Cell%20Value%20(1st%20drop%20down)%2C%20equal%20to%20(2nd%20drop%20down)%2C%20and%20type%200%20in%20the%203rd%20box.%20Then%20click%20Format%20and%20under%20the%20Fill%20Tab%20choose%20the%20color%20you%20want%20the%20empty%20cells%20to%20be%20highlighted%20(in%20your%20case%2C%20yellow).%20Click%20OK%20and%20OK%20again%20in%20the%20initial%20rule%20window.%20This%20allows%20you%20to%20highlight%20cells%20that%20don't%20have%20information%20and%20send%20a%20digital%20file%20to%20be%20filled%20out.%20Once%20the%20person%20fills%20in%20the%20required%26nbsp%3Bcells%2C%20the%20yellow%20highlight%20is%20removed%20and%20can%20be%20printed.%20Good%20luck!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-106800%22%20slang%3D%22en-US%22%3ERe%3A%20Shade%20a%20cell%20but%20not%20print%20that%20color%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-106800%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Joel%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20guess%20in%20general%20that's%20possible%20using%20VBA%20script%20if%20in%20Workbook_BeforePrint%20event%20remove%20the%20color%20from%20the%26nbsp%3Brange%20and%20in%20Worbook_AfterPrint%20return%20the%20color%20back.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESomethig%20like%20that%2C%20but%20this%20is%20just%20an%20idea.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-106798%22%20slang%3D%22en-US%22%3ERe%3A%20Shade%20a%20cell%20but%20not%20print%20that%20color%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-106798%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20Logaraj%20for%20the%20helpful%20suggestion!%20This%20is%20a%20fantastic%20work-around.%20But%20unless%20there%20is%20another%20solution%2C%20I%20presume%20that%20I%20can't%20BOTH%20hide%20the%20one%20shaded%20color%20but%20otherwise%20print%20my%20sheet%20in%20color.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-93199%22%20slang%3D%22en-US%22%3ERe%3A%20Shade%20a%20cell%20but%20not%20print%20that%20color%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-93199%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F65128%22%20target%3D%22_blank%22%3E%40Joel%20Lee%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGo%20to%20Page%20Setup%20-%26gt%3B%20Sheet%20Tab%20-%26gt%3B%20Enable%20Checkbox%20Black%20and%20White%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20453px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F18182i4219552CEA1B07D2%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Untitled4.png%22%20title%3D%22Untitled4.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1125597%22%20slang%3D%22en-US%22%3ERe%3A%20Shade%20a%20cell%20but%20not%20print%20that%20color%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1125597%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F99423%22%20target%3D%22_blank%22%3E%40morkimb%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20been%20looking%20for%20a%20solution%20to%20this%20issue%20forever%20so%20thank%20you%20for%20this%20info!%26nbsp%3B%20Follow%20up%20question...%20what%20about%20a%20cell%20where%20the%20value%20that%20needs%20to%20be%20entered%20is%20%240.00%3F%26nbsp%3B%20It%20seems%20if%20that's%20the%20case%2C%20the%20cell%20just%20remains%20highlighted%20no%20matter%20what.%26nbsp%3B%20Please%20help%20if%20you%20can.%26nbsp%3B%20Thank%20you!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1126593%22%20slang%3D%22en-US%22%3ERe%3A%20Shade%20a%20cell%20but%20not%20print%20that%20color%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1126593%22%20slang%3D%22en-US%22%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F530262%22%20target%3D%22_blank%22%3E%40Amber-Lee%3C%2FA%3E%3CBR%20%2F%3EI%20would%20try%20changing%20the%20conditional%20formatting%20to%20Rule%20Type%20%E2%80%9CFormat%20only%20cells%20that%20contain%E2%80%9D%20then%20change%20the%20first%20drop%20down%20under%20Rule%20Description%20from%20%E2%80%9Ccell%20value%E2%80%9D%20to%20%E2%80%9Cblanks%E2%80%9D.%20That%20way%20any%20data%20entered%20would%20trigger%20the%20highlighted%20cell%20to%20turn%20white%20once%20filled.%20Hope%20this%20helps!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1126595%22%20slang%3D%22en-US%22%3ERe%3A%20Shade%20a%20cell%20but%20not%20print%20that%20color%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1126595%22%20slang%3D%22en-US%22%3EI%20will%20try%20that.%20Thank%20you!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1323117%22%20slang%3D%22en-US%22%3ERe%3A%20Shade%20a%20cell%20but%20not%20print%20that%20color%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1323117%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F530262%22%20target%3D%22_blank%22%3E%40Amber-Lee%3C%2FA%3E%26nbsp%3B%20Dood...%20This%20has%20been%20sooo%20helpful.%20Thank%20you.%26nbsp%3B%20Now%2C%20considering%20I%20am%20using%20this%20for%20a%20field%20form%2C%20there%20are%20fields%20that%20are%20shaded%20until%20filled%20in%3B%20however%2C%20I%20will%20have%20many%20fields%20that%20do%20not%20get%20filled%20in.%26nbsp%3B%20Is%20there%20a%20way%20to%20create%20a%20conditional%20format%20for%20fields%20that%20are%20not%20filled%20in%20(e.g.%2C%20won't%20change%20the%20field%20from%20shaded%20to%20blank)%2C%20but%20I%20still%20want%20all%20shading%20to%20go%20away%20upon%20printing%2Fsaving.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20would%20be%20acceptable%20if%20the%20shading%20did%20not%20go%20away%20when%20saved%20as%20an%20xlsm%20extension%2C%20but%20when%20later%20saved%20to%20pdf.%26nbsp%3B%20Is%20that%20possible%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20happy%20for%20solutions.%20Thanks.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Is there a way I can shade a cell, but keep that cell from being shaded when I print the sheet? For example, I want someone to enter data only in the cells I've shaded yellow, but I want the finished print to not have yellow, shaded cells.

10 Replies

Hi @Joel Lee,

 

Go to Page Setup -> Sheet Tab -> Enable Checkbox Black and White

 

Untitled4.png

Thank you Logaraj for the helpful suggestion! This is a fantastic work-around. But unless there is another solution, I presume that I can't BOTH hide the one shaded color but otherwise print my sheet in color.

Hi Joel,

 

I guess in general that's possible using VBA script if in Workbook_BeforePrint event remove the color from the range and in Worbook_AfterPrint return the color back.

 

Somethig like that, but this is just an idea.

Joel, try using conditional formatting (Home, Styles, Conditional Formatting). Select the cells you want to be highlighted yellow and click Conditional Formatting, New Rule. Under Rule Type select "Format only cells that contain" and choose Cell Value (1st drop down), equal to (2nd drop down), and type 0 in the 3rd box. Then click Format and under the Fill Tab choose the color you want the empty cells to be highlighted (in your case, yellow). Click OK and OK again in the initial rule window. This allows you to highlight cells that don't have information and send a digital file to be filled out. Once the person fills in the required cells, the yellow highlight is removed and can be printed. Good luck!

This is the solution.

@morkimb 

I've been looking for a solution to this issue forever so thank you for this info!  Follow up question... what about a cell where the value that needs to be entered is $0.00?  It seems if that's the case, the cell just remains highlighted no matter what.  Please help if you can.  Thank you!

@Amber-Lee
I would try changing the conditional formatting to Rule Type “Format only cells that contain” then change the first drop down under Rule Description from “cell value” to “blanks”. That way any data entered would trigger the highlighted cell to turn white once filled. Hope this helps!
I will try that. Thank you!

@Amber-Lee  Dood... This has been sooo helpful. Thank you.  Now, considering I am using this for a field form, there are fields that are shaded until filled in; however, I will have many fields that do not get filled in.  Is there a way to create a conditional format for fields that are not filled in (e.g., won't change the field from shaded to blank), but I still want all shading to go away upon printing/saving.  

 

It would be acceptable if the shading did not go away when saved as an xlsm extension, but when later saved to pdf.  Is that possible?

 

So happy for solutions. Thanks.

 

@Pledesma012 You could use Conditional Formatting to accomplish that.

 

1. click on New Rule

2. Rule Type Format only cells that contain

3. Format only cells with set to Specific Text and use N/A

4. Click on Format, Then Font Tab and set color to White

 

OR

 

1. click on New Rule

2. Rule Type Use a formula to determine which cells to format

3. Enter formula =EXACT( "NA", A1 ) or =EXACT( "N/A", A1 )

4. Click on Format, Then Font Tab and set color to White