Referencing Non-bold in a formula

  • Thread starter Thread starter bob
  • Start date Start date
B

bob

If AU2 is greater than AU1 and if AU2 contains non-bold formatting, I want to
return the answer "BAD" in AW2. My current formula is set up only for the
first argument but not the non-bold part:
=IF(AU2>AU1,"BAD",0)

Any thoughts on how to do this?

Thanks,
Bob
 
You would need a user defined function (UDF) to do this but there is a
caveat to consider. If the cell is bolded as result of conditional
formatting what I'm about to suggest won't work. Also, if the cell is
manually bolded, changing a cell format does not trigger a calculation so
any formula will not update when the format is changed and will only update
when some other event triggers a calculation.

In my humble opinion you should not base any calculations on a cell format
for the reasons I've noted above. You should create a formula based on the
logic of *why* the cell is bolded. That being said...

Create this UDF.

Option Explicit
Function IsBold(ACell As Range) As Boolean
Application.Volatile
If ACell.Font.Bold Then
IsBold = True
Else
IsBold = False
End If
End Function

Paste the code above into a general module.

Then your formula would be:

=IF(AND(COUNT(AU1:AU2)=2,AU2>AU1,NOT(IsBold(AU2))),"Bad",0)
 
You can stick this UDF in a module in your workbook then use the formula below.

Function CountBold(rg As Range) As Long
''originally posted by Ron Rosenfeld
Dim c As Range
For Each c In rg
CountBold = CountBold - c.Font.Bold
Next c
End Function


=IF(AND(AU2>AU1,CountBold(AU2)<>1),0,"BAD")


Gord Dibben MS Excel MVP
 
Back
Top