Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

2 Answers 2

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 at 13:06

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.