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.
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.
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).