# Function for determine if cell has 'mingled bold text'

1. ## 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.

2. ## 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)

3. ## 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

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

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

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

One more:
``Please Login or Register  to view this content.``

6. ## 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. ## Re: Function for determine if cell has 'mingled bold text'

Thank you too shg!

8. ## 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. ## 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. ## 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. ## 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.

##### Users Browsing this Thread

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

#### 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