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.

 

[Rick Rothstein]

Remark: the code above works if A1 is updated mannualy. A formula in A1 won't trigger the event.

We can probably work around that problem once we know what cells the formula referernces, which is why I asked the several questions that I did in Message #8.

 

[Marcelo Branco]

We can probably work around that problem once we know what cells the formula referernces, which is why I asked the several questions that I did in Message #8.

If there is a formula in A1 *maybe* the property DirectDependents can help.

M.

 

[hiker95]

BAMF,

See:

Re: Underline Text in a Formula, reply #4
pgc01, MrExcel MVP
http://www.mrexcel.com/forum/excel-questions/350376-underline-text-formula.html

 

[Rick Rothstein]

If there is a formula in A1 *maybe* the property DirectDependents can help.

Another way besides that (especially if there are cell references to other worksheets) would be to replace the formula and add VB code to imitate what the formula does, then add the formatting to that code... that would give you the most control.

 

[Marcelo Branco]

Another way besides that (especially if there are cell references to other worksheets) would be to replace the formula and add VB code to imitate what the formula does, then add the formatting to that code... that would give you the most control.

Yes, you are right.

M.