Conditional formatting to hide cell content even when printed

Question

I would like to set up a conditional formatting setting that would hide the contents (the cell should look blank) if the cell's contents is equal to another cell. Does anyone know of a function to do this? I tried just making the font the same color as the background (gray, in this case), but unfortunately when this is printed, there is some sort of residue shadow left over from the text.

Here is what happens when I do gray on gray (they are supposedly the same color):

I am using Excel 2008 on the Mac.

Clerk

Jeff, is there a reason you haven't accepted any of the offered answers? I personally think @mpalanco's is the most-robust and fairly simple. – Buffo

Answer 1

Below in A2 we have the conditional formatting that hides the content of that cell if it is equal to B2.

In Conditional Formatting create the following rule: =$A$2=$B$2, then press Format...

1. On the Format menu, click Cells, and then click the Number tab.
2. Under Category, click Custom.
3. In the Type box, type ;;; (that is, three semicolons in a row), and then click OK.

Unsustainable

I can't believe that ;;; is correct. It's a great answer, but it's maddening that Microsoft have done this sort of hacky workaround. – Yarvis

@DanAtkinson Yes, it is quite surprising. The reason behind is that custom number formats consist of 4 sections separated by 3 semicolons, so if we just type 3 semicolons and nothing else, the cell will appear empty. – Unsustainable

Thanks for the info and link! – Yarvis

Answer 2

another way around this is a hack but it should work:

• have two sheets, data and view
• base your conditional formatting on the view sheet off of values in the data sheet (not sure if this is an option...)
• use if statements in each cell that say something like if(dataCell=matchCell, "", dataCell)

Campy

Answer 3

I suspect that the printing problem is not so much a problem with Excel, but a feature of the printer. Printer manufacturers obviously want to get the "best" result that they can on a printed page. Some will optimise colour by doing things like making smaller volumes of print (e.g. charters and lines) a bit darker and large areas (e.g. cell backgrounds a bit lighter). This is so that when they are side-by-side they look like the same colour. However, when printed on top of one another as per your case, you get a residual effect.

This stems from a well known effect that for any given colour, a large area of that colour will look darker than a small area of the exact same colour. There are other effects that give this illusion - such as the background and nearby colours. For an example - that is not exactly the same as yours - but illustrates the illusion, search the web for "adelson's checkerboard" and read the explanations.

In any event, the solution proposed by mpalanco (use conditional formatting of three semi-colons) is the easiest way to achieve clean result - every time, printed to paper, printed to pdf, on screen and any other form.

Murielmurielle

Answer 4

You are on right track to resolve this. You just need to make the font color white this will not show the value when printed on paper unless the paper is not white. I have done this with Office 2007.

Expect

Thanks Amit, but the background is gray. Switching the text to gray makes the text disappear on the screen, but when printed, there is a bit of a shadow left over that looks weird. – Clerk

I hope you have already checked the other font attribute (like emboss etc) and removed on the text. What about writing a macro and calling on cell value change. – Expect

Where do I change the emboss (and others) settings? I don't see it in the Format dialog box. Also, this version of Excel doesn't support Macros. Time for Microsoft to start supporting all features in both versions. :) – Clerk

FYI: font color and embossing alone do not work. I use white-font on white-background and when I produce a report, there's a smudge of gray. This should be the right answer but unfortunately is not sufficient. – Buffo

Answer 5

So I think that to get the effect that you would like, you will need to use a macro to check the contents of the cell. A worksheet_change event should work well.

To get this code functional, I will need you to tell me the range of cells whose values should be hidden, and the range of cells that we are checking the values against.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range.Address Then
    For Each VariantValue in OtherRange
        If Range.Value = VariantValue Then
           Range.NumberFormat = ";;;"
        End If
    Next
End if
End Sub

http://www.extendoffice.com/documents/excel/919-excel-hide-cell-contents.html

http://www.ozgrid.com/VBA/run-macros-change.htm

Reproduce