Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Printing - Not All Conditional Shading Printing

0

I have conditional formatting shading in my spreadsheet. Even though I can see the shading on my page only some of them will print.  Others, even in print view, do not appear.  Any help would be appreciated.

Answer
Discuss

Discussion

There are three questions here. One, are the formats correctly set. Two, can your monitor display the requested colours. Three, can your printer print the requested colours. For one, we need to see your workbook. For two, we need to know about your monitor. For three, we need to know about your printer.
However, it's not that complicated - thanks God - because this site is about Excel. We aren't suposed to know about monitors and printers. But if you share your workbook as part of your question (you can append it in Edit mode) we shall be glad to take a look. Bear in mind that the first step toward a cure is to be able to replicate the result in another environment. Here's a chance to try.
Variatus (rep: 4889) Mar 1, '21 at 7:03 pm
Add to Discussion

Answers

0

Hi

Firstly I was surprised that you're using a file for 2018 dates but assume you have an equivalent for 2021 which has the same problem

If was very puzzling but I think the somewhat convoluted "EXACT" rule for conditional formatting of holidays is causing some printing problem (and I could not get say February shading to show in print preview or when printed).

For February for example, $B$21:$AD$28 has two rules and the one for orange shading is:

=EXACT((ADDRESS($AL$6,$AM$6)),(CELL("address",B$21)))
On the sheet, 19 Feb (2018) appears  shaded orange but it's unshaded in print preview or if printed to a pdf.

When I changed the rule to the simpler:

=IF(B$21=DAY($AI$6),1)
the shading worked! To stick with your method, wrapping the EXACT with an IF (and removing the spare sets of brackets) also works- change the rule for $B$21:$AD$28 to read this (additions in bold):
=IF(EXACT(ADDRESS($AL$6,!$AM$6),CELL("address",B$21)),1)
and 19 Feb will appear in print preview etc. I've done (just) that in the attached revision to your file.

Suggest you try this elsewhere (in your 2021 file) but noting you have overlapping conditional formatting  rules for some ranges like:

Formula; Range; Interior.color

=EXACT((ADDRESS($AL$11,$AM$11)),(CELL("address",B$113))) ; $B$113:$AE$120 ; 48

=EXACT((ADDRESS($AL$12,$AM$12)),(CELL("address",B$113))) ; $B$113:$AE$120  ; 3

=EXACT((ADDRESS($AL$13,$AM$13)),(CELL("address",B$113)))  ; $B$113:$AE$120   ;3

Note that when I did the same for March, it didn't seem to work (but I rushed that). It might be better to change the conditional formatting rules (per month) to pick out the dates from a list of holidays (like the =IF(B$21=DAY($AI$6),1) I used above, possibly the an OR statement if there are several dates in a given month).

Discuss

Discussion

Thank you for the quick response.  I will give it a try and get back to you.
mmcginnis (rep: 2) Mar 2, '21 at 8:44 am
Thanks but note the comment about March and trying different formatting rules. If you share your 2021 file, Variatus or I might see something else (but I've never had an Excel problem with this before) 
John_Ru (rep: 6102) Mar 2, '21 at 9:25 am
I was able to use the If statement you recommended and it fixed the problem.  I still have another problem.  Thank you again!

Example: =IF(B$10=DAY($AI$5),1)

Since Good Friday appears in March or April I had to use:  =IF(AND((B$34=DAY($AI$7)),(3=MONTH($AI$7))),1)

This is an evolving spreadsheet.  I plan to eventually modify the dates in the holiday box so you can place anything in the box from any month and it will find it on the spreadsheet and color accordingly (ie. vacationd days in blue).

Not sure how to add the updated file to this post.
mmcginnis (rep: 2) Mar 2, '21 at 11:38 am
This seems like a separate question (so please raise one and add the file).

If the IF solution worked, kindly mark my Answer as Selected (so others know that issue is fixed) 
John_Ru (rep: 6102) Mar 2, '21 at 11:56 am
If you're wanting to shade the holidays, I think contributors might suggest easier ways. 
John_Ru (rep: 6102) Mar 2, '21 at 12:01 pm
So what did you find please/ are you posting a fresh question?
John_Ru (rep: 6102) Mar 3, '21 at 9:16 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login