Is there a macro that can render the contents of a cell invisible to
both view and to the printer? For example, if cell A10, 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

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

--


Gary


wrote in message
oups.com...
Is there a macro that can render the contents of a cell invisible to
both view and to the printer? For example, if cell A10, 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

Hi

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

Thanks
Satish

Gary Keramidas wrote:
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

--


Gary


wrote in message
oups.com...
Is there a macro that can render the contents of a cell invisible to
both view and to the printer? For example, if cell A10, 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

Hi Michael,

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

MP

a écrit dans le message de news:
...
Is there a macro that can render the contents of a cell invisible to
both view and to the printer? For example, if cell A10, 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

Michel,

Thanks for your response. It works great with one exception. I need
to assign A1 two different values: A1=1 and A11. 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


Michel Pierron wrote:
Hi Michael,

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

MP

a écrit dans le message de news:
...
Is there a macro that can render the contents of a cell invisible to
both view and to the printer? For example, if cell A10, 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

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

Gary Keramidas wrote:
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

--


Gary


wrote in message
oups.com...
Is there a macro that can render the contents of a cell invisible to
both view and to the printer? For example, if cell A10, 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

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

a écrit dans le message de news:
...
Michel,

Thanks for your response. It works great with one exception. I need
to assign A1 two different values: A1=1 and A11. 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


Michel Pierron wrote:
Hi Michael,

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

MP

a écrit dans le message de news:
...
Is there a macro that can render the contents of a cell invisible to
both view and to the printer? For example, if cell A10, 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

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


--


Gary


wrote in message
ps.com...
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

Gary Keramidas wrote:
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

--


Gary


wrote in message
oups.com...
Is there a macro that can render the contents of a cell invisible to
both view and to the printer? For example, if cell A10, 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

Michel,

Thank you so much. It works great.

Michael