Conditional format

  • Thread starter Thread starter Ian Coates
  • Start date Start date
I

Ian Coates

Is there a way of hiding data using conditional formatting on both screen
and print? Setting the font to white hides it on screen, but it still prints
black.

TIA

Ian
 
Ian,

I was surprised to hear you say that, so I did a quick test. And guess what,
the white formatted (Yes via CF) text did not print on my system (XP, Excel
2000).

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Ian

I find that if Print Setup>Sheet is set to "black and white" the white text
will print black.

Otherwise not. Try unchecking that.

Gord Dibben Excel MVP
 
I have to admit I was surprised, too. I'm using 2000 versions of Office and
Windows and it definitely causes a problem here.

I have a lot of recoding to do on the affected spreadsheets, so I think I'll
have to write some more code in. I can set it to remove the text (or in most
cases formulae) instead of chaning the text color. That'll do it.
 
Unfortunately, there are multiple users on multiple PCs with differing
printer types to contend with. I'm going to add some code to remove the
text/formulae instead of hiding it with formatting.

Also, there isn't an option to print black and white when there is only a
black cartridge (which we use mostly) so I can't set it to print in colour
:-(

Thanks anyway. I was just hoping for an esier option that VBA.

C'est la vie

Ian
 
Ian

Unfortunate but necessary, it seems.

Gord

Unfortunately, there are multiple users on multiple PCs with differing
printer types to contend with. I'm going to add some code to remove the
text/formulae instead of hiding it with formatting.

Also, there isn't an option to print black and white when there is only a
black cartridge (which we use mostly) so I can't set it to print in colour
:-(

Thanks anyway. I was just hoping for an esier option that VBA.

C'est la vie

Ian
Click to expand...
 
The 'Black and white' setting should be available, no matter what
printer you've selected.

Choose File>Page Setup
On the Sheet tab, remove the check mark from 'Black and White'

Or, change the setting with code:

ActiveSheet.PageSetup.BlackAndWhite = False
 
-----Original Message-----
Is there a way of hiding data using conditional formatting on both screen
and print? Setting the font to white hides it on screen, but it still prints
black.

TIA
Click to expand...
Ian

AFAIK you can not do this with conditional format but it
is possible to hide cells with say zero values and these
don't show in print preview so I supose they will not
print.

The following sub hides cells in column 3 that have a zero
value if this helps.

Sub HideZeros()
Dim rng As Range, c As Variant
Dim i As Long, nr As Long, j As Integer
nr = Application.WorksheetFunction.CountA(Range("A:A"))
Set rng = Range(Cells(1, 3), Cells(nr, 3))
' make sure all cells are visible
rng.EntireRow.Hidden = False
For Each c In rng
If c.Value = 0 Then
c.EntireRow.Hidden = True
End If
Next c

End Sub

Regards
Peter
 
Peter Atherton said:
....
The following sub hides cells in column 3 that have a zero
value if this helps.

Sub HideZeros()
Dim rng As Range, c As Variant
Dim i As Long, nr As Long, j As Integer
nr = Application.WorksheetFunction.CountA(Range("A:A"))
Set rng = Range(Cells(1, 3), Cells(nr, 3))
' make sure all cells are visible
rng.EntireRow.Hidden = False
For Each c In rng
If c.Value = 0 Then
c.EntireRow.Hidden = True
End If
Next c

End Sub
Click to expand...

Seems excessive. First, if you're going to iterate through column C (as in
the 2nd arg to Cells), why are you counting the entries in column A rather
than column C? Second, if there are any blank cells in the column counted,
nr will miss the last few rows of entries. Better to use

nr = Range("C:C").Cells(Rows.Count, 1).End(xlUp).Row

Finally, hiding zeros is hardly the same as hiding entire rows containing
zero in one particular column. Easier to do and undo to change the number
format to, say,

#,##0.00_);(#,##0.00);;@

Note that the format between the second and third semicolons is nothing.
 
Back
Top