+ Reply to Thread
Results 1 to 11 of 11

Function for determine if cell has 'mingled bold text'

  1. #1
    Registered User
    Join Date
    12-25-2009
    Location
    The Netherlands
    MS-Off Ver
    Excel 2003/2007
    Posts
    43

    Function for determine if cell has 'mingled bold text'

    I have the following formula (taken from the web) in my spreadsheet which let me know if a cell have bold text or not. But when the cell has 'mingled' text, i.e. only partly bold, he gave a #VALUE error.

    Please Login or Register  to view this content.
    My question is: Do someone know how to change this formula, so when a cell has 'mingled' text, the result is 'TRUE' and not #VALUE.
    I enclose an example spreadsheet.

    Thanks.
    Attached Files Attached Files
    Last edited by Excelfriend; 01-11-2010 at 04:24 PM.

  2. #2
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Function for determine if cell has 'mingled bold text'

    I wrote a function similar to this to test for strikethrough characters, here it is edited for your BOLD test:

    Please Login or Register  to view this content.
    One side effect of this approach is that you can also now use it to test if ALL the cells in a range all have a bold character in it:

    =ISBOLD(A1:A10)
    Last edited by JBeaucaire; 01-11-2010 at 04:32 PM. Reason: Added the volatility flag
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Function for determine if cell has 'mingled bold text'

    Hello Excelfriend,

    The Font.Bold statement can return 3 different values. The UDF is returning a boolean value. When the characters are mixed, you get the error because you are attempting to assign a NULL value to a Boolean value.

    TRUE - if all characters are bold.
    FALSE - if no characters are bold
    NULL - if some characters are bold
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  4. #4
    Valued Forum Contributor blane245's Avatar
    Join Date
    02-20-2009
    Location
    Melbourne, FL
    MS-Off Ver
    Excel 2010
    Posts
    649

    Re: Function for determine if cell has 'mingled bold text'

    How about this?
    Please Login or Register  to view this content.
    cell.Font.Bold is returning null on mingled text.

  5. #5
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Function for determine if cell has 'mingled bold text'

    One more:
    Please Login or Register  to view this content.
    Entia non sunt multiplicanda sine necessitate

  6. #6
    Registered User
    Join Date
    12-25-2009
    Location
    The Netherlands
    MS-Off Ver
    Excel 2003/2007
    Posts
    43

    Re: Function for determine if cell has 'mingled bold text'

    JBeaucaire, Thank you for you quick solution. This works nice!

    Leith Ross,
    Thank you for your comment. I was not aware that the FONT.BOLD statement can return 3 values.

    Thank you too blane245, your formula change is working !

  7. #7
    Registered User
    Join Date
    12-25-2009
    Location
    The Netherlands
    MS-Off Ver
    Excel 2003/2007
    Posts
    43

    Re: Function for determine if cell has 'mingled bold text'

    Thank you too shg!

  8. #8
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Function for determine if cell has 'mingled bold text'

    Everyone else's suggestions had a volatility flag in it, so I checked mine and found it needs one, too, to be 100%. So I added it above in post #2. Be sure to add it in...

    Jerry

  9. #9
    Registered User
    Join Date
    12-27-2009
    Location
    Paris, France
    MS-Off Ver
    Excel 2003
    Posts
    64

    Re: Function for determine if cell has 'mingled bold text'

    Hello,

    An other way

    Please Login or Register  to view this content.
    With regards.

    PMO
    Patrick Morange

  10. #10
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Function for determine if cell has 'mingled bold text'

    I was not aware that the FONT.BOLD statement can return 3 values.
    Many properties will return Null if the data is not the same for all members of the collection, e.g., someRange.NumberFormat, someRange.Style, ...

  11. #11
    Registered User
    Join Date
    12-25-2009
    Location
    The Netherlands
    MS-Off Ver
    Excel 2003/2007
    Posts
    43

    Re: Function for determine if cell has 'mingled bold text'

    PMO, thank you too Patrick!
    Your formula has as added point in it 'mingled'! So this is indeed helpful.

+ 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