Results 1 to 6 of 6
  1. #1
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts

    Conditional Formatting - Changing Font Color Changes Font Face

    I have a worksheet with a column of cells whose font color changes if the cell has a value above 95,000,000. The cells are formatted as 10 point Verdana, with their color AUTOMATIC.

    The formatting rule is Cell Value > 95000000. When the rule is TRUE, the font color changes from AUTOMATIC to one of the greens in the "standard colors" at the bottom of the color palette. Everything works fine.

    When I edit the rule to have the color change to something else, Excel changes the font face! The new color comes up fine when the cell contents is above the threshold value, but the font face isn't Verdana. I can't tell what it is, but it's a serif font that isn't as wide as Verdana. It appears to be 10 pt size, but I can't be sure.

    In the Format Cells dialog box, there's a line that says "For Conditional Formatting you can set Font Style, Underline, Color, and Strikethrough". Nothing is mentioned about Font Face. I can definitely set all those things, and they are activated when the rule is TRUE.

    As expected, there's no place in the dialog box to set the Font Face. Excel seems to set that itself.

    What's going on here?
    Last edited by Lou Sander; 2015-05-27 at 17:17. Reason: Clarify
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  2. #2
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts
    More...

    The font to which the cell changes seems to be Cambria 10 point. That is the "Headline" font that shows up when I look at the Fonts for my current Theme. I don't know much about themes, and I don't care about them except when they mess around with my worksheets. They seem to be the creation of some Microsoft marketing person who thinks I/we care a lot about having snazzy-looking documents.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Does it help if you use a custom number format rather than conditional formatting:
    [Color10][>95000000]#,##0;General
    for example?
    Regards,
    Rory

    Microsoft MVP - Excel

  4. The Following User Says Thank You to rory For This Useful Post:

    Lou Sander (2015-05-28)

  5. #4
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts
    Very interesting! This variety of special formatting is new to me.

    It definitely changes the color when the cell contains something higher than the trigger. It definitely does NOT change the font face. This is what I'm looking for, but there are two problems:

    I need there to be a dollar sign in front of everything.

    The number format when the cell contains something LOWER than the trigger needs to be the same as with something higher ($99,000,000). I don't know how to do this, but am eager to learn.

    =====================

    Also, on further cogitation, maybe what I REALLY want is for the cell fill color to change when the $95 million threshold is exceeded. It would definitely be OK to do the font color, though.

    I've dug a little deeper, and the font changing seems to be connected to the Theme attached to the workbook. When I changed the theme to "Aspect", which has Verdana as its font, the "font changing" problem went away. The Excel Help on themes doesn't explain this stuff in very great depth for Excel.

    I have a book, Word 2010 Inside Out, which has deep coverage of themes. I'll look at it and see if it sheds any light. I think it will.
    Last edited by Lou Sander; 2015-05-28 at 09:44.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  6. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    If you want the fill colour, you'll have to go back to using CF. Otherwise you can use:
    [Green][>95000000]$ #,##0_ ;$ #,##0
    as a custom number format to show the $ signs and apply the same formatting to all numbers.
    Regards,
    Rory

    Microsoft MVP - Excel

  7. The Following User Says Thank You to rory For This Useful Post:

    Maudibe (2015-05-28)

  8. #6
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts
    That does it! I'll use Special Formatting for now, and think more about CF and fill colour.

    The world of Excel is an endless one, and fascinating.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

Posting Permissions

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