Print the workbook in colour but not the cell highlights

[beca]

Hi, I have a workbook with colour logos, and some cells have highlighted colours. I want to print the workbook showing the colour logos and everything, but not the cell highlights.

Is there a way to do this?

 

[bobsan42]

Are the cell highlights conditional formatting or manually filled?
If it is conditional formatting then yes, there is a relatively easy way.
Otherwise a macro has to loop through all cells, save the formatting, then clear it, then print, then return the formatting to the cells.

 

[beca]

Hi, the cell highlights are a mix of manual and conditional formatting. A bit of pain.

 

[bobsan42]

What I do when I want to print like this is select a cell, usually A1, and put a value in it, 1 or TRUE.
Then include its value in the conditional formatting. Whe I change its value to 0 or FALSE all conditional formatting disappears. Then I can print.

 

[beca]

Thanks Bob, what about manual highlights? In fact, conditional formatting colours are not shown, it only highlights when the value is out of the range.

It is the manual highlights that I want to remove before printing.

Is there a macro code to do this kind of task?

 

[bobsan42]

Here is a code - 2 subs, one saves the cells colors and clears them the second one restores them.
it's sort of a quick draft and far from refined but it works. Since the colors are saved in a variable you should restore them before it is lost.
My next idea is to store them in an additional hidden sheet, but I can't promise when I will write it.

Option Explicit

Const sfx = "_col"
Dim cArr
Dim Csaved As Boolean

Sub saveColors()
    If Csaved Then
        If vbNo = MsgBox("Colors are already saved. Do you want to erase the saved colors?", vbYesNo + vbQuestion + vbDefaultButton2) Then
            Exit Sub
        End If
    End If
    On Error Resume Next
    Csaved = False
    Erase cArr
    
    Dim wb As Workbook
    Set wb = ThisWorkbook
    Dim sh As Worksheet, sh2 As Worksheet
    Set sh = wb.ActiveSheet
    Dim rng As Range, cc As Range
    Set rng = sh.UsedRange
    
    Dim i As Long, j As Long
    ReDim cArr(0 To 2, 0 To 0)
    
    For Each cc In rng
        With cc
            If .Interior.ColorIndex <> xlNone Then
                ReDim Preserve cArr(0 To 2, 0 To i)
                cArr(0, i) = .Parent.Name
                cArr(1, i) = .Address(0, 0)
                cArr(2, i) = .Interior.Color
                .Interior.ColorIndex = xlNone
                i = i + 1
            End If
        End With
    Next cc
    If i > 0 Then Csaved = True Else cArr = Null
ep:
    On Error Resume Next
    Set wb = Nothing
    Set sh = Nothing
    Set rng = Nothing
    Set cc = Nothing
End Sub

Sub ResetColors()
    If Not IsArray(cArr) Then
        MsgBox "No colored cells are saved. Cannot restore them."
        Exit Sub
    End If
    On Error Resume Next
    
    Dim wb As Workbook
    Set wb = ThisWorkbook
    Dim sh As Worksheet, sh2 As Worksheet
    Set sh = wb.ActiveSheet
    Dim rng As Range, cc As Range
    Set rng = sh.UsedRange
    
    Dim i As Long, j As Long
    
    With sh
        For i = LBound(cArr, 2) To UBound(cArr, 2)
            .Range(cArr(1, i)).Interior.Color = cArr(2, i)
        Next i
    End With
    Erase cArr
    Csaved = False
ep:
    On Error Resume Next
    Set wb = Nothing
    Set sh = Nothing
    Set rng = Nothing
    Set cc = Nothing
End Sub

 

[Gokhan Aycan]

Double posting btw, print workbook in colour but not the manual highlighted cells

I gave an answer but don't have the time to address OP's concern atm.