+ Reply to Thread
Results 1 to 11 of 11

Header doesn't show conditional formatting

  1. #1
    Registered User
    Join Date
    08-08-2018
    Location
    Tyskland
    MS-Off Ver
    2013
    Posts
    4

    Header doesn't show conditional formatting

    Tried to look this up but this specific issue was not addressed yet.

    I have a table as shown in the first picture. Top left corner address is C7. Top row is not part of the table and is plain without any formatting, dark brown row is a header row for the table.
    I made two conditional formats, other formula based on C7 value, other on C8 value as shown in the second picture.

    Blue cells are result of conditional formatting for C7.
    Capture.PNG

    Formulas
    captuer2.PNG

    For an unknown reason the conditional formatting doesn't work with the cell C8 which is in the header row. Why is that, and how can I get it to work?


    Cheers!

    EDIT: I just realized how misleading the headline is, apologies!!
    The problem simply is: When using HEADER cell C8 in the CF rule ($C$8=1), conditional formatting doesn't work. In this case cells D10:D12 should turn to RED as the cell contains value 1.
    I tried this with another empty workbook and CF works fine without headers, but as soon as a table with headers is introduced, all CFs will disappear if the rule is based on header cell.
    Last edited by Jackxel; 08-09-2018 at 02:53 AM. Reason: Problem re-defined

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,489

    Re: Header doesn't show conditional formatting

    Please attach the workbook, not a picture of it.

    Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,901

    Re: Header doesn't show conditional formatting

    Your range that you applying CF to, only starts in 10. try extending the ranges
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,489

    Re: Header doesn't show conditional formatting

    Doh!!! I should have gone to Specsavers ...

  5. #5
    Registered User
    Join Date
    08-08-2018
    Location
    Tyskland
    MS-Off Ver
    2013
    Posts
    4

    Re: Header doesn't show conditional formatting

    Quote Originally Posted by FDibbins View Post
    Your range that you applying CF to, only starts in 10. try extending the ranges
    Only after reading this I figured my problem statement was misleading big time. See the edit for corrected problem. Thanks for swift reply tho!

  6. #6
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Re: Header doesn't show conditional formatting

    Try checking Stop If True in your CF.

    Or attach a sample workbook.

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,489

    Re: Header doesn't show conditional formatting

    See post #2. Without seeing the workbook, there is not a lot any of us can do.

  8. #8
    Registered User
    Join Date
    08-08-2018
    Location
    Tyskland
    MS-Off Ver
    2013
    Posts
    4

    Post Re: Header doesn't show conditional formatting

    Oh I was wondering if it's my computer or something blocking the attachment link. I created a new workbook that has exactly the same setup with explanations. Hopefully that works.
    Attached Files Attached Files
    Last edited by AliGW; 08-09-2018 at 04:36 AM.

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,489

    Re: Header doesn't show conditional formatting

    Headers in a table are always text (you can see that they are left-aligned), so you will need this:

    =--I$1=DAY(TODAY())

    The double unary (--) forces the formula to see the text as a value.

  10. #10
    Registered User
    Join Date
    08-08-2018
    Location
    Tyskland
    MS-Off Ver
    2013
    Posts
    4

    Re: Header doesn't show conditional formatting

    It works!! I could never have figured that out, neither those 20+ google top search results did. Thanks
    Last edited by AliGW; 08-09-2018 at 05:11 AM.

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,489

    Re: Header doesn't show conditional formatting

    It's only when I saw the left-aligned cells in your attachment that I remembered about table headers being text - run into it myself in the past using dates as table headers!

    Glad to have helped!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Inserting New Row with VBA doesn't Update all Conditional Formatting
    By klenny in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-15-2018, 04:24 AM
  2. Replies: 2
    Last Post: 05-29-2017, 11:03 AM
  3. Replies: 7
    Last Post: 01-23-2016, 04:16 AM
  4. [SOLVED] Conditional Formating - show "1" for Blank and any keywords which doesn't show in text
    By suchetherrah in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-15-2015, 07:28 AM
  5. Excel 2007 : AND doesn't work in Conditional Formatting
    By cellarir in forum Excel General
    Replies: 2
    Last Post: 03-21-2012, 06:10 PM
  6. [SOLVED] Why doesn't header show on first page?
    By dodhner in forum Excel General
    Replies: 0
    Last Post: 07-17-2006, 09:30 AM
  7. [SOLVED] linked cell doesn't show formatting
    By Elizabeth in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-02-2005, 03:06 PM

Tags for this Thread

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