Render the contents of a cell invisible

[mrlanier]

Is there a macro that can render the contents of a cell invisible to
both view and to the printer? For example, if cell A1>0, then the
contents of range B1:B5 are rendered invisible. The formulas and their
returns cannot be altered or made null due to their applications in
other cells. Thanks.

Michael

 

[Gary Keramidas]

maybe just making the font white?

Sub invisible()
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
With ws
If .Range("A1") > 0 Then
.Range("B1:b5").Font.ColorIndex = 2
Else
.Range("B1:b5").Font.ColorIndex = 0
End If
End With
End Sub

 

[Satish]

Hi

That will work if the paper is white, but not if the paper is of any
other color.

Thanks
Satish

 

[Michel Pierron]

Hi Michael,

Sub Invisible()
With Worksheets(1)
..Range("B1:B5").NumberFormat = IIf(.Range("A1") <> "", ";;;", "General")
End With
End Sub

MP

 

[mrlanier]

Michel,

Thanks for your response. It works great with one exception. I need
to assign A1 two different values: A1=1 and A1>1. With your macro,
when I enter 1, the contents of range B1:B5 does exactly what I had
asked for. However, when I delete the contents of A1 or change the
value to something other than 1, I cannot recall the contents of range
B1:B5, which is something I need to do. Do you have a solution?
Thanks.

Michael

 

[mrlanier]

Thanks Gary. The problem I have is my printer is a B&W laser printer,
therefore, no matter what the color of the font is, it still prints
black. I actually need the print to be invisible to the printer. If
you have a solution, it would be much appreciated. Thanks.

Michael

 

[Michel Pierron]

Hi Michael,
In the Sheet module:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("B1:B5").NumberFormat = IIf([A1] = 1, ";;;", "General")
End Sub

Regards,
MP

<[email protected]> a écrit dans le message de (e-mail address removed)...
Michel,

Thanks for your response. It works great with one exception. I need
to assign A1 two different values: A1=1 and A1>1. With your macro,
when I enter 1, the contents of range B1:B5 does exactly what I had
asked for. However, when I delete the contents of A1 or change the
value to something other than 1, I cannot recall the contents of range
B1:B5, which is something I need to do. Do you have a solution?
Thanks.

Michael

 

[Gary Keramidas]

the other solution is probably better, but when i print a page, nothing in b1:b5
prints on my laser printer.

 

[mrlanier]

Michel,

Thank you so much. It works great.

Michael