Does Excel not display when printing?

Question

I have a book like this:

With conditional formatting to highlight my first ten numbers in column U.

My book has the following vba codes:

And this code that disables the calculation and reactivates the calculation:

Option Explicit Sub code2() MsgBox "This will take upto 2 minutes." Application.ScreenUpdating = False Dim WB As Workbook Dim I As Long Dim j As Long Dim Lastrow As Long Dim WeekNum As Integer 'Clear Data Sheet On Error GoTo Message With ThisWorkbook.Worksheets("Data") .Rows(2 & ":" & .Rows.Count).ClearContents End With On Error Resume Next Set WB = Workbooks("LO Lines Delivery Tracker.xlsm") On Error GoTo 0 If WB Is Nothing Then 'open workbook if not open Set WB = Workbooks.Open("G:\WH DISPO\(3) PROMOTIONS\(18) LO Delivery Tracking\LO Lines Delivery Tracker.xlsm") End If ' ======= Edit #2 , also for DEBUG ====== With WB.Worksheets(1) Lastrow = .Cells(.Rows.Count, "G").End(xlUp).Row j = 2 For I = 7 To Lastrow WeekNum = CInt(Format(.Range("G" & I).Value, "ww", 2) - 1) ' === For DEBUG ONLY === Debug.Print CInt(ThisWorkbook.Worksheets(2).Range("B9").Value) Debug.Print WeekNum Debug.Print CInt(ThisWorkbook.Worksheets(2).Range("D9").Value) Debug.Print Year(.Range("G" & I).Value) Debug.Print ThisWorkbook.Worksheets(2).Range("B6").Value Debug.Print .Range("M" & I).Value If CInt(ThisWorkbook.Worksheets(2).Range("B9").Value) = WeekNum Then ' check if Month equals the value in "A1" If CInt(ThisWorkbook.Worksheets(2).Range("D9").Value) = Year(.Range("G" & I).Value) Then ' check if Year equals the value in "A2" If ThisWorkbook.Worksheets(2).Range("B6").Value = .Range("M" & I).Value Then ThisWorkbook.Worksheets(3).Range("A" & j).Value = .Range("G" & I).Value ThisWorkbook.Worksheets(3).Range("B" & j).Formula = "=WeekNum(A" & j & ",21)" ThisWorkbook.Worksheets(3).Range("C" & j).Value = .Range("L" & I).Value ThisWorkbook.Worksheets(3).Range("D" & j).Value = .Range("D" & I).Value ThisWorkbook.Worksheets(3).Range("E" & j).Value = .Range("E" & I).Value ThisWorkbook.Worksheets(3).Range("F" & j).Value = .Range("F" & I).Value ThisWorkbook.Worksheets(3).Range("g" & j).Value = .Range("p" & I).Value ThisWorkbook.Worksheets(3).Range("H" & j).Value = .Range("H" & I).Value ThisWorkbook.Worksheets(3).Range("I" & j).Value = .Range("I" & I).Value ThisWorkbook.Worksheets(3).Range("J" & j).Value = .Range("J" & I).Value ThisWorkbook.Worksheets(3).Range("k" & j).Value = .Range("Q" & I).Value ThisWorkbook.Worksheets(3).Range("L" & j).Value = .Range("m" & I).Value j = j + 1 End If End If End If Next I End With Application.Calculation = xlAutomatic ThisWorkbook.Worksheets("Data").UsedRange.Columns("B:B").Calculate ThisWorkbook.Worksheets(2).UsedRange.Columns("B:AA").Calculate On Error GoTo Message With ThisWorkbook.Worksheets(2) '<--| change "mysheet" to your actual sheet name Intersect(.Range(Rows(14), .UsedRange.Rows(.UsedRange.Rows.Count)), .Range("G:G")).WrapText = True Intersect(.Range(Rows(14), .UsedRange.Rows(.UsedRange.Rows.Count)), .Range("G:G")).EntireRow.AutoFit End With End ThisWorkbook.Worksheets(2).Activate Application.ScreenUpdating = True Exit Sub Message: On Error Resume Next Exit Sub End Sub 

I do not know if this is due to the fact that I turn calclulations on and off, but when I want to print this sheet, even if I print as pdf. Conditional formatting is not displayed.

Can someone show me what I'm doing wrong?

Edit: I also tried adding this to the book:

 Private Sub Workbook_BeforePrint(Cancel As Boolean) For Each wk In Worksheets wk.Calculate Next End Sub 

It still does not work.

Answer 1

Regarding your actual question: “What am I doing wrong?”, I think the answer may be “Nothing!”

If formatting is displayed when you start printing (because you have already performed the calculation), Excel should print it; and the color in the legend in your print preview shows that there are no print options that prevent color from printing. Thus, it is likely that it just Excel is not behaving correctly.

I usually did not answer this question, because there is usually a reason why something happens, but not printing visible formatting seems like a real problem. I suggest the following research and workarounds:

Try manually translating the calculations back to Automatic before attempting to print. If this is a correction, then it confirms that Excel is having problems combining conditional formatting with manual calculation (although this should not). In this case, a possible solution is to create a separate print button that automatically calculates the calculations, prints with the current settings, and then returns the calculation to automatic.

Another solution requiring a bit more effort is to replace conditional formatting with VBA. I expect this to work, since a manual print application still seems printable. Your code just needs to skip each row of data, compare the value in the current row with all rows to see if it is in the top 10, and then set the background color for all cells in the row.