Can you format text with a formula?

[BAMF]

Hello everyone! I'm tying to automate something relatively simple, but I am getting my butt handed to me. I need to underline text which is populated from a formula. The formula is quite simple but the formatting is killing me. I know how to format numbers, dollars and dates within a formula, but I can't figure out how to underline text. Please help!

Desired Result
The employee's who sold more than 100 cars this month are: Alex, Mike and Tim. Please congratulate them on their performance!

Formula being used
="The employee's who sold more than 100 cars this month are: "&cell reference&"Please congratulate them on their performance!"

Current result
The employee's who sold more than 100 cars this month are: Alex Mike Tim Please congratulate them on their performance!

Attempts
I wish underlining text were an option within Format Cells/Custom so I could just use the formula =text(cell reference, "underline"). However, I don't see that as an option in the 2007 or 2010 versions. Is there a way to add "underline text" as my own custom format?

I also tried using "Conditional Formatting" (both "Format cells only that contain" and "Use a formula to determine which cells to format)." I can't quite get it because I don't know how to list names for the former conditional formatting. Vlookup is the only way I know how to refer to a list for the formula based conditional formatting, but this would stop after the first name on the list is found and ignore the rest of them. If a guru knows of a way to automate this formatting issue I'd be happy to buy him a beer! Thank you.

 

[Gary's Student]

You cannot underline parts of the results of a formula. The best you can do is to copy the formula and paste/special/values back onto the cell. You can then format parts of resulting text as underlined.

 

[BAMF]

Bummer. I really figured since I can format numbers with =text(A1,"MM/DD/YY), or really any date and/or slash ("/") combination I desire, that underlining would be less of a stretch. It doesn't feel like a stretch that conditional formatting would also be able to do it, I just can't figure out the correct formula configuration. Is all hope really lost on this idea? Thank you

 

[maxwalford2]

You can record a macro to underline a cell (very easy, try it and you will agree) and you should be able to set the macro to run when the cell is updated (less easy but look into it anyway)

 

[Gary's Student]

An interesting idea Max.

 

[BAMF]

Hey Max, thank you for your reply. I played around with a few macro's after your suggestion. I can get it to underline all of a cell, but not parts of a sentence or parts of a formula within a cell (as Gary said). I don't use macro's that often so it is possible I am just not doing it right, but the underline option disappears once I select within the formula bar on an active cell. CTRL+U doesn't appear to work either. Pretty tough little sentence to automate... grr

 

[maxwalford2]

You might have to get stuck into some code and search the string for the names required.

 

[Rick Rothstein]

Hey Max, thank you for your reply. I played around with a few macro's after your suggestion. I can get it to underline all of a cell, but not parts of a sentence or parts of a formula within a cell (as Gary said). I don't use macro's that often so it is possible I am just not doing it right, but the underline option disappears once I select within the formula bar on an active cell. CTRL+U doesn't appear to work either. Pretty tough little sentence to automate... grr

You would have to use event code (still VBA, but not a macro) to take the place of your formula's calculation so it can insert a text constant into the cell which it can then format for you. But to do this, you would need to show us the fomula, tell us the starting row or column the fomula is place in, tell us whether the formula was copied down or across and describe any formatting it might currently have (if any) besides the underlining that you want.

 

[Marcelo Branco]

Following Max idea.

See if this does what you want
Assumes
A1 --> cell reference, ie contains Alex, Mike and Tim.
C1 --> message cell
Adjust to suit

Right-click on sheet tab pick View code and paste the code below in the right-panel

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" Then
        With Range("C1")
            .Value = "The employee's who sold more than 100 cars this month are: " & _
            Target.Text & ". Please congratulate them on their performance!"
                
            .Characters(Start:=60, Length:=Len(Range("A1").Value)).Font. _
                Underline = xlUnderlineStyleSingle
        End With
    End If
End Sub

M.

 

[Marcelo Branco]

Remark: the code above works if A1 is updated mannualy. A formula in A1 won't trigger the event (maybe we can try a workaround...)

M.