+ Reply to Thread
Results 1 to 4 of 4

Excel 2007 : Way to make a font transparent?

  1. #1
    Forum Contributor
    Join Date
    06-02-2005
    Posts
    100

    Way to make a font transparent?

    Hi All.

    Been using the conditional color formating features on some data columns. Would like to make the text invisible somehow but keep the conditial color formating.

    Is there a way to set a FONT color to transparent, or perhaps to automatically set the font color to the same color as is being set by the conditial cell color?

    Thanks

    -o

  2. #2
    Valued Forum Contributor Miraun's Avatar
    Join Date
    04-03-2009
    Location
    New England
    MS-Off Ver
    2003, 2007, 2010, 2013
    Posts
    554

    Re: Way to make a font transparent?

    Hi Ouka;

    Long answer is that it'll depend on the type of conditional formatting you're using.

    If you're using the new 2007 Data-bar formatted style, then you can easilly remove the text/numbers by selecting the "Show Bar Only" in the conditional formatting menu box.

    If you're using Icon Style, you can remove the text/number by selecting, "Show Icon Only"

    For dual-colored or patterned cells, unfortunately, I'm not sure.

    For single colored background cells, simply go to the conditional format set, and select the format button. This will bring up the all too familiar format cells menu. From here, it sounds like you've already selected the color for your fill, just click on the Font tab and select the same color for the Font. That way, it'll format the text/number to be the same color as the background.

    Good luck
    Going for Guru! Click the Star to the bottom left of this post if I helped!

  3. #3
    Forum Contributor
    Join Date
    06-02-2005
    Posts
    100

    Re: Way to make a font transparent?

    Thanks for the reply. We weren't using the bar colors, we used:

    Conditional Formatting - Color Scales - More Rules - Format all cells based on their values - 2-Color Scale.

    So it shades our selected cells based on their numerical values using the sliding color scale.

    Problem is we don't want the text to show. And since it's a color gradient, the few, if any of the hundreds of cells we have colored match the pre-defined color types available in the font palette.

    Is there a way to get the RBG value of each cell .Interior and then set the font color to the same value?

  4. #4
    Valued Forum Contributor Miraun's Avatar
    Join Date
    04-03-2009
    Location
    New England
    MS-Off Ver
    2003, 2007, 2010, 2013
    Posts
    554

    Re: Way to make a font transparent?

    Hi again Ouka;

    So, I found some VBA that may do just that... at least determines the color of the cell. I don't know if it would dynamically work with conditional formatting just because I'm not sure how conditional formatting parameters are stored within the cell value.

    However...

    It was then that I stepped back for a moment. You can set the conditional formatting of a cell to be based upon another cell's value. So if A1 = Yes, you can make E1 red, while if A5 were No, E5 could be blue... This workaround may be easier than (what one of the moderators called me out on doing before) cracking open a nut with a sledgehammer.

    Unfortunately, you didn't post a sample of the data, so I'm unable to see in what capacity you're looking to use the conditional formatting, but would a simple workaround be to add in a column to the right of the calculated cell for the formatting, and apply the format/color to that column?

    If not, and VBA really is the only way, I found this website:
    http://www.cpearson.com/excel/colors.aspx
    Which has some example VBA for extracting colors of cells, which I should be able to leverage to create you something if there's no other way around it. But that's mainly for my own reference.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1