How do I show True as a value in Excel B2 if Cell B1 is bold?

[Guest]

I want to be able to determine whether the text in any given cell is bold,
and display that information as a true/false value in an adjoining cell.

Can you tell me how to accomplilsh this?

Thanks.

 

[JE McGimpsey]

One way:

Put this in a regular code module:

Public Function IsBold(rRng As Range) As Boolean
Dim rCell As Range
Dim bTemp As Boolean
Application.Volatile
If rRng.Count = 1 Then
IsBold = rRng.Font.Bold
Else
bTemp = True
For Each rCell In rRng
bTemp = bTemp And rCell.Font.Bold
If Not bTemp Then Exit For
Next rCell
IsBold = bTemp
End If
End Function


Since changing format doesn't trigger a calculation, this can only be
guaranteed to be accurate after recalculating the sheet (and is the
reason for the Application.Volatile statement).

If you're not familiar with UDF's see

http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Bob Phillips]

You can use a UDF

Function IsBold(rng As Range)
Application.Volatile
If rng.Count > 1 Then
IsBold = CVErr(xlErrRef)
Else
IsBold = rng.Font.Bold
End If
End Function

and use like =IsBold(A1)

but it doesn't automatically upadte if the cell changes, you need to F9.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

The long and the short of it is that you can only sort of do this. It
requires a user defined function that only sort of does what you asked. The
problem is that changing a cell format does not re-caclulate the sheet. You
want is a formula to evaluate to True or False, but it won't recalc
automatically. Here is some code that you can put in the same place as you
would find a recorded macro.

Public Function IsBold(ByVal TargetCell As Range) As Boolean
Application.Volatile
IsBold = TargetCell.Font.Bold
End Function

In the cell that you want to evaluate to true or false type in the formula

=IsBold(A1)

This will return true or false depending on the font in A1. The problem
arises if the font in A1 is changed. The formula will not re-evaluate until a
calculation is run (press F9 to see what I mean)...