+ Reply to Thread
Results 1 to 8 of 8

Conditional Formatting Erroneously Inserts The Header Text

  1. #1
    Registered User
    Join Date
    09-07-2012
    Location
    Australia
    MS-Off Ver
    Excel for Mac 16.661 (22101101)
    Posts
    31

    Conditional Formatting Erroneously Inserts The Header Text

    Hi,
    I have a Options Trading spread sheet which has a column (E5:E35) with the Header (in merged cells E3 & 4) with the text Call Put. I added two conditional formatting rules to E5:E35 which color the cells green when I type the letter C and red when I type P.
    Strange thing is when I type the C, Excel inserts the header text Call Put (green BG and text) instead of just C. The rule works when I type P (goes red).
    When I change the header text to Put Call the rule works for C but not for P. Somehow Excel reacts to the first letter of the header even though the 2 header cells (3 and 4) are not included in the cell range 5:35.
    What is it that I am missing here. I know it's something simple. Any ideas?

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,463

    Re: Conditional Formatting Erroneously Inserts The Header Text

    Could you share us what the two CF formulas are?

    Make sure you stand in first cell E5 to apply CF formula, also "Applies to" with absolute range $E5:$E35

    I suppose you made a mistake that you stood in E7 and "Applies to" E5:E35 (relative reference), so CF in E5 became "Applies to" E3:E33, cause problem.
    Quang PT

  3. #3
    Registered User
    Join Date
    09-07-2012
    Location
    Australia
    MS-Off Ver
    Excel for Mac 16.661 (22101101)
    Posts
    31

    Re: Conditional Formatting Erroneously Inserts The Header Text

    Thanks for your reply.
    I shift-selected E5:E35 for both rules and went to Conditional Formatting => Highlight Cell Rules => Text That Contains.
    Attached is a screenshot of the Manage rules window and one of Edit Rule, when entering C. (I hope they get through, a bit of a novice at this)
    Can send reduced excel sheet with the problem column if necessary

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,681

    Re: Conditional Formatting Erroneously Inserts The Header Text

    There are instructions at the top of the page explaining how to attach your sample workbook.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  5. #5
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,463

    Re: Conditional Formatting Erroneously Inserts The Header Text

    Choose the header range, then Home tab/ Clear/Clear format.

  6. #6
    Registered User
    Join Date
    09-07-2012
    Location
    Australia
    MS-Off Ver
    Excel for Mac 16.661 (22101101)
    Posts
    31

    Re: Conditional Formatting Erroneously Inserts The Header Text

    Clearing format doesn't seem to work.
    I've attached the sheet. As you can see with the header text saying Put Call, when trying to enter the letter P into cell E16, Excel enters Put Call instead just the letter P.
    If I change the header to Call Put, the opposite happens, instead of just C I get Call Put instead.
    This applies to the whole column E5:E35.
    Weird!
    Attached Files Attached Files

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,681

    Re: Conditional Formatting Erroneously Inserts The Header Text

    Not weird - it's autofill (Excel trying to be helpful).

    https://support.microsoft.com/en-us/...a-86f8d4f9fe20

  8. #8
    Registered User
    Join Date
    09-07-2012
    Location
    Australia
    MS-Off Ver
    Excel for Mac 16.661 (22101101)
    Posts
    31

    Re: Conditional Formatting Erroneously Inserts The Header Text

    Thanks AliGW, that's fixed it.
    The Autofill option in my Excel 16.5 for Mac is in a slightly different place, but I found it and changed the setting.
    Problem solved, much appreciated.
    GB

+ 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. [SOLVED] Conditional Formatting usng Two Columns Header
    By Deddiph in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 11-09-2020, 07:23 AM
  2. [SOLVED] VBA Conditional Formatting using Column Header
    By Deddiph in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 10-04-2020, 10:12 AM
  3. [SOLVED] Header doesn't show conditional formatting
    By Jackxel in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 08-09-2018, 05:11 AM
  4. [SOLVED] Conditional formatting using lookup table and combination of column and header value
    By uPhone in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-27-2017, 05:53 AM
  5. Conditional formatting on pivot field header
    By dgharvey in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-13-2013, 08:25 AM
  6. [SOLVED] Selecting the field header for conditional formatting
    By BenExcel in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 04-12-2013, 01:38 PM
  7. Formulas Erroneously Appearing as Text
    By ExcelTip in forum Tips and Tutorials
    Replies: 0
    Last Post: 08-23-2005, 03:37 AM

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