Why is conditional formatting in an .xlsm not printing?

Question

I have an xlsm workbook that colours cell backgrounds based on comparison to adjacent worksheets. The adjacency is always 1 to the left, irrespective of whether sheets are inserted or deleted. The process is a bit convoluted, but is as simple as I could find a way of doing it.

Basically a cell will compare itself to the cell in the same location on the previous sheet, if is is the same, there is no change to the format, if it is different, then the cell is highlighted. The only VBA is in a named range, 'sheetnames' '=GET.WORKBOOK(1)&T(NOW())'. It appears to return a long string with all sheet names.

The condition to compare against the previous sheet is:

=INDIRECT(ADDRESS(ROW(),COLUMN(),4,1,INDEX(MID(sheetnames, FIND("]",sheetnames)+1,255), SHEET()-1))) <>INDIRECT(ADDRESS(ROW(),COLUMN(),4,1,))

This uses the referenced 'sheetnames' and extracts the name of the previous sheet, using it an indirect address builder.

The formatting works perfectly on screen, but does not appear in the print preview, the physical print or the pdf export. Any ideas on why this might be the case?

Further if there is a simpler way of addressing adjacent sheets, I'd welcome the input.

Answer 1

The problem is in the formula, namely the use of SHEET(). As used, no sheet is specified inside the parentheses so the Excel convention (often seen, for instance, with CELL) of using the currently selected cell as the cell to evaluate the fnction "relative to" is used.

This seems to lead to a circular reference. In older Excel functionalities, such a formula will evaluate ONCE and never again, or if it does, will give the circular reference error. So it evaluates nicely when rendering the conditional formatting for the screen, but when Printing (apparently) asks for everything to be evaluated prior to printing, it fails and so the conditional formatting is lost to processes downstream from the Printing until the Printing effort is finished.

That does require that any such request by the Printing functionality does not change the spreadsheet or its rendered display, just Printing's use of the material it is trying to print, and its discarding of it after finishing its efforts. But that does not seem unlikely.

Specify the sheet you are on, which does not seem an impossibly beastly chore... a chore, admittedly, but not a beastly one even if you have 200 tabs, and the formula provides nicely for both the Conditional Formatting AND for the printing.

Another consideration that could lead to sorrow is that the plan for the formula ONLY works when the cell holding the formula is the currently selected cell. Select a different cell and while it may seem to work, it is not. Any seeming to work aspect is shear coincidence as you are comparing not the intended cells, but quite a different pair. Like a painting of a clockface and hands on a wall, it is not a clock and it is not "um, duh, right twice a day, dude" because it is NOT a clock. Same here.

Just a simple edit of the formula to something like SHEET("sheet2") solves the problem.

So it comes down to having to completely refigure how to achieve your comparison, which is a certain amount of work, and maybe close to impossible without VBA (probably not, but maybe), vs. the other certain amount of work to modify the formula.

As to that, one could form the formula as a text string, using the output of SHEET() in the string, then copy, paste as value, and copy inside the Formula Editor, then paste the copied string as a formula in the cell you wish, copying and pasting THAT if needed in more than one cell (as its addressing agnostic). Do that in a Named Range even. In any case, then the deed of clicking between the parentheses and pasting or typing the current sheet name wouldn't need doing even.

Finally, to be explicit, the VBA (Excel 4 macro command, but that counts as VBA nowadays) is in no way, shape, or form at fault here. It is solely the circularity brought in via the use of SHEET without a parameter.