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.


4 Answers 4


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

Here's the same cells with the custom format:

custom font format

  • Great this works like anything
    – Dev_Man
    Jan 30, 2019 at 10:16
  • 1
    If you read this after almost 10 years, thanks! How in the heck did you figure this out? Nov 24, 2022 at 14:30
  • I don't recall how I stumbled across the original answer-either google-jitsu or plain, old fashioned trial and error. Regardless, it's come in handy more times that I can count.
    – dav
    Dec 19, 2022 at 18:58

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, 2013 at 12:13
  • That's too bad. Responses in this StackOverflow post suggest an approach. Will check out later today.
    – chuff
    May 22, 2013 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, 2013 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, 2017 at 17:32

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


This is how I do it.

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

You must log in to answer this question.

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