Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I'm creating a heatmap using Excel's conditional formatting. I don't want the cell's values to show against the color background. Is there a way to make the font's formatting transparent, similar to what is found in the cell's fill formatting?

Since it's a heat map, I can't use a single color (e.g. white) and I'd prefer to not have to individually format each cell's font property to the appropriate color.

EDIT: To help clarify, the heat map is colored using Conditional Formating>Color Scale>Red-White-Blue (where white was modified to a light gray), so the cell fill value is not directly set, rather it is a calculated value on a color gradient.

Heatmap

share|improve this question
up vote 1 down vote accepted

While I'm not aware of any worksheet functionality to match a cell's font color to its fill color (other than manually), it's very easy to do with a macro. The first macro below changes the font color(s) in the cells of a selected range to match their fill colors. The second returns the font colors back to the default black.

Sub HideFont()
    Dim cell As Variant
    For Each cell In Selection
        cell.Font.Color = cell.Interior.Color
    Next cell
End Sub

Sub UnhideFont()
    Dim cell As Variant
    For Each cell In Selection
        cell.Font.Color = 0
    Next cell
End Sub

To install the macros, select Developer / Visual Basic from the main ribbon and then choose Insert / Module from the menu. Paste the code into the edit pane that opens up. The macros will appear in the macro list accessible by choosing Developer / Macros from the main ribbon. Just select with the mouse the range you want to modify and choose the macro you want to run.

share|improve this answer
    
Very interesting approach. I tried it and it works in a noramlly formated filled cell (i.e. where the cell format is directly selected). Unfortunately, it doesn't work where the cell color is a conditional format on a gradient-then it just changes to white. Any thoughts? – dav May 22 '13 at 12:13
    
That's too bad. Responses in this StackOverflow post suggest an approach. Will check out later today. – chuff May 22 '13 at 14:31
    
Thanks, for now I'm using my number format cheat, but I'm going to work up the VBA referenced in your link to get a better long-term solution. – dav May 23 '13 at 16:26

I've found a work-around that doesn't change the font color, but effectively removes the text from the cells. Adjust the cell number format to Custom, with a value of ;;;.

share|improve this answer
    
wow, this is good.. – Firee Mar 12 '14 at 13:06

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .