Excel: Underline Partial Text in a cell 1

[ShabanaHafiz]

I have an Excel Sheet, Sheet1, which prints donation receipt. Required Data is entered in Sheet2. Sheet1 has a cell, in which user can enter row number of Sheet2. Then Donation Receipt is populated with the data of that row.

What I need to do is underline partial text so as to exactly simulate manual receipt. For example, cell B25 has the following formula:

="Received with thanks from " &Name

Name is a range and refers to Sheet1!$L$4.

The sample output in B25 is
Received with thanks from SHABANA HAFIZ

Needed output is:
Received with thanks from SHABANA HAFIZ

 

[Fenrirshowl]

Unless anyone can suggest anything else, I would suggest eliminating the problem -
1) Move the "Received with thanks from " to a cell further to the right and right align this new cell.
2) Pull the name through into the cell immediately to the right of that one, and format it with the underline on.

Fen

 

[xlbo]

yup - 'bout the only way to do this - you cannot individually format text if it is the result of a formula

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[GlennUK]

You could do it completely in VBA, by using an application event for the worksheet:

Private Sub Worksheet_Calculate()
    Dim strFirstPart As String, iLenFirst As Integer
    strFirstPart = "Received with thanks from "
    iLenFirst = Len(strFirstPart)
    Application.EnableEvents = False
    Range("B25").Value = strFirstPart & ActiveSheet.Range("Name")
    For i = iLenFirst + 1 To Len(Range("B25").Value)
         Range("B25").Characters(i, 1).Font.Underline = xlUnderlineStyleSingle
    Next
    Application.EnableEvents = True
End Sub

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[ShabanaHafiz]

Thanks.

I tried this code. It is showing Application-defined or object-defined error on this line:

Range("B25").Value = strFirstPart & ActiveSheet.Range("Name")

I tried the following and it worked:

Range("B25").Value = strFirstPart & "SHABANA HAFIZ"

But it is not working with named range.

 

[GlennUK]

Is the named range in the active sheet?

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[stevexff]

ActiveSheet.Range("Name")[red].Value[/red]

perhaps?

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[ShabanaHafiz]

Thanks Glenn.

Named range was not in the active sheet.

For testing purpose, I defined range Name with string value in Refers to.

With the original scenario, where Name refers to Sheet1!$L$4, it worked perfectly.

Thanks a lot.

 

[GlennUK]

My pleasure. :-D

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.