Using underlined text as a condition in IF formula

[andyreinlo]

Hello,

I have a situation where I would like underlined text to be used in an IF logical test. I realise using the normal font formatting isnt ideal but i would like to get this working for my situation. From reading other posts, it seems I will need to use VBA to add a module. Adapting from another thread I have this...

Public Function IsUnderline(c As Range) As Boolean
On Error GoTo Handler
IsUnderline = c.Font.Underline
Exit Function
Handler:
If Err.Number = 94 Then IsUnderline = True
End Function

As a test, my formula currently is

=IF(ISUNDERLINE(D3),0,"")

The issue is it seems to display 0 in the cell regardless of the text is underlined or not. Can anyone please advise? I have never really used VBA before so it may be something obvious but not to me!
Thank you.

 

[RoryA]

The Font.Underline property is not a boolean (there are multiple types of underline). You could use something like:

Public Function IsUnderline(c As Range) As Boolean
application.volatile true
   On Error GoTo Handler
   IsUnderline = c.Font.Underline <> xlUnderlineStyleNone
   Exit Function
Handler:
   If Err.Number = 94 Then IsUnderline = True
End Function

Note that you need the function to be volatile to have any chance of it ever being accurate. Changing the underlining of a cell does not cause a recalculation. Making the function volatile means that it will at least recalculate whenever any open workbook does.

 

[duggie33]

I was just about to say something similar...

Here is a reference: XlUnderlineStyle enumeration (Excel)

Doug

 

[andyreinlo]

The Font.Underline property is not a boolean (there are multiple types of underline). You could use something like:

Public Function IsUnderline(c As Range) As Boolean
application.volatile true
   On Error GoTo Handler
   IsUnderline = c.Font.Underline <> xlUnderlineStyleNone
   Exit Function
Handler:
   If Err.Number = 94 Then IsUnderline = True
End Function

Note that you need the function to be volatile to have any chance of it ever being accurate. Changing the underlining of a cell does not cause a recalculation. Making the function volatile means that it will at least recalculate whenever any open workbook does.

Thankyou for the quick reply. I know this isnt an ideal solution, but didnt realise it wouldnt be 'live' and display the desired results in real time. Is there another way I can achieve this so that the true/false of the IF function are display as soon as the font formatting is changed?

 

[RoryA]

Not really, other than perhaps having a routine constantly running on a timer. It is generally not a good idea to use formatting as data in Excel. Is there no way you can use actual data (e.g. in a helper column) to reflect the data that should be underlined? Then you can use conditional formatting to do the underlining based on the data, and you can also use formulas based on the data, which will update in real time.