Need to either print without printing cell fill color or need to undo in a macro

[OleMiss2010]

I built a worksheet that has certain cells that need to be filled in. These are identified by having a gray fill. However, I want to print the worksheet without the gray fill to save toner. I have two ideas. Either I do something that ignores the fill color in printing (I have no idea how to do this or if it is possible) or write a macro so that the user clicks a button within the worksheet whenever they are ready to print. My subroutine would look like the following (only I need to fill in one gap):

[pre]

Sub PrintWorksheet()
Cells.Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
ActiveWindow.SelectedSheets.PrintOut Copies:=1

'Here is my issue. I need a line that would undo the previous color change.
'I can do this outside of the macro, but not within the macro.

End Sub

[/pre]

 

[OleMiss2010]

Right after I posted this I came up with as solution on my own. I know that the only unlocked cells are those that are gray. With that in mind I changed my macro to:

[pre]

For Each Cell In Range("A1:J600")
Cell.Select
If Selection.Locked = False Then
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.149998474074526
.PatternTintAndShade = 0
End With
End If
Next Cell

[/pre]

If there is a more efficient way to do this please let me know.

 

[Luke M]

My suggestion would be to define the cells as a NamedRange. I.e., select all the cells that are gray, and give them a name like "ColoredCells". Then your macro will run much faster with this bit:

[pre]

With Range("ColoredCells").Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.149998474074526
.PatternTintAndShade = 0
End With

[/pre]

 

[OleMiss2010]

There is some variability to which cells it will be on any given worksheet. The only things that will certainly be true are that the cells will not be locked and they'll be in that range.

 

[Luke M]

Just to double check, are the cells naturally formatted gray, or is it conditionally formatted to be gray?

 

[Luke M]

Borrowing from here:

http://spreadsheetpage.com/index.php/tip/selecting_all_unlocked_cells/

This should be faster:

[pre]

Sub SelectUnlockedCells()
Dim WorkRange As Range
Dim FoundCells As Range
Dim Cell As Range
Set WorkRange = ActiveSheet.UsedRange
For Each Cell In WorkRange
If Cell.Locked = False Then
If FoundCells Is Nothing Then
Set FoundCells = Cell
Else
Set FoundCells = Union(FoundCells, Cell)
End If
End If
Next Cell
If FoundCells Is Nothing Then
MsgBox "All cells are locked."

Else
With FoundCells
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.149998474074526
.PatternTintAndShade = 0
End With
End If
End Sub

[/pre]

 

[OleMiss2010]

I'm putting the entirety of my code below. I augmented what you gave me because I know that there will always be some cells that are not locked, so FoundCells should never be nothing after running the first If, Then. Still I'm getting an "Object does not support this property or method" error at

[pre]

With FoundCells
.Pattern=xlSolid
This makes me think the FoundCells Range is not getting filled correctly.

Any thoughts?

Sub PrintWorksheet()
Dim WorkRange As Range
Dim FoundCells As Range
Dim Cell As Range

Cells.Select

With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With

ActiveWindow.SelectedSheets.PrintOut Copies:=1

Set WorkRange = ActiveSheet.UsedRange

For Each Cell In WorkRange
If Cell.Locked = False Then
If FoundCells Is Nothing Then
Set FoundCells = Cell
Else
Set FoundCells = Union(FoundCells, Cell)
End If
End If
Next Cell

With FoundCells
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.149998474074526
.PatternTintAndShade = 0
End With

Range("B3").Select

End Sub

[/pre]

 

[OleMiss2010]

Problem Solved.


Here's the updated code:

[pre]

Sub PrintWorksheet()
Dim WorkRange As Range
Dim FoundCells As Range
Dim Cell As Range

Cells.Select

With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With

ActiveWindow.SelectedSheets.PrintOut Copies:=1

Set WorkRange = ActiveSheet.UsedRange

For Each Cell In WorkRange
If Cell.Locked = False Then
If FoundCells Is Nothing Then
Set FoundCells = Cell
Else
Set FoundCells = Union(FoundCells, Cell)
End If
End If
Next Cell

'I forgot that you have to make changes to the interior of a selection.
'With that in mind I selected the FoundCells Range and then changed the interior.

FoundCells.Select

With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.149998474074526
.PatternTintAndShade = 0
End With

Range("B3").Select

End Sub

[/pre]

 

[OleMiss2010]

Thanks for all of your help Luke M, as always.

 

[Luke M]

No problem. If you want to save one step, you can change this:

[pre]

FoundCells.Select

With Selection.Interior
to this:

With FoundCells.Interior

[/pre]
but it's very minor. Only different is that you don't have to select the cells.(Generally, removing all the selects in a code help speed things up) I forgot to callout the interior part in my first post.

 

[OleMiss2010]

Good point.