18

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

29

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 ;;;.

  • Great this works like anything – Dev_Man Jan 30 at 10:16
3

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.

  • 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
  • Sorry about changing the accepted answer, but with the vote disparity it seemed appropriate. Also, it matches the method I regularly use now. – dav Jan 17 '17 at 17:32
1

Okay, so this is the first time I have ever submitted code, so here goes. I thought the macro route would be the way to go, but as you can't set the font to be the same as the cell color using conditional formatting then the only other way would be to change both with a macro that works similar to the effect of the conditional format, see below:

Sub change()

    Dim Rstart, Rmid, Rend, Gstart, Gmid, Gend, Bstart, Bmid, Bend, Rsd, Rdd,_
    Gsd, Gdd, Bsd, Bdd, Rcell, Gcell, Bcell As Integer
    Dim maxsel, minsel, halfsel, halfval, v As Double



    Rstart = 0
    Rmid = 230
    Rend = 255
    Gstart = 0 
    Gmid = 230
    Gend = 0
    Bstart = 255
    Bmid = 230
    Bend = 0

    Rsd = Rmid - Rstart
    Rdd = Rend - Rmid

    Gsd = Gmid - Gstart
    Gdd = Gend - Gmid

    Bsd = Bmid - Bstart
    Bdd = Bend - Bmid

    maxsel = Application.WorksheetFunction.Max(Selection)
    minsel = Application.WorksheetFunction.Min(Selection)
    halfsel = (maxsel - minsel) / 2
    halfval = minsel + halfsel
    If halfval = 0 Then Exit Sub

    Dim cell As Variant
    For Each cell In Selection
        v = cell.Value
        If v >= minsel And v < halfsel Then
            Rcell = Round((Rstart + ((halfval - v) / halfsel) * Rsd), 0)
            Gcell = Round((Gstart + ((halfval - v) / halfsel) * Gsd), 0)
            Bcell = Round((Bstart + ((halfval - v) / halfsel) * Bsd), 0)
        Else
            Rcell = Round((Rmid + ((v - halfval) / halfsel) * Rdd), 0)
            Gcell = Round((Gmid + ((v - halfval) / halfsel) * Gdd), 0)
            Bcell = Round((Bmid + ((v - halfval) / halfsel) * Bdd), 0)
        End If

    cell.Font.Color = RGB(Rcell, Gcell, Bcell)
    cell.Interior.Color = RGB(Rcell, Gcell, Bcell)

    Next cell

    End Sub

Hope this helps someone, even though it's three years too late for the original question.

1

This is how I do it.

.Cells(RowTo, ColHcpDiP).Font.Color = .Cells(RowTo, ColHcpDiP).Interior.Color 'Set color invisible

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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