Conditional formatting to hide cell content even when printed

I would like to set up a conditional formatting setting that would hide the contents (the cell should look bl开发者_如何学编程ank) 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.

---

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.

---

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)

---

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.

---

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.

---

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