Apply format only a part of the cell values

**jmaterano** · 12-14-2010, 10:50 AM

Hi How are you?

How do I format just part of the text obtained from the CONCATENATE function for example if I want the word Maria is in bold and the profession is in italics.

I have put into practice the conditional formatting but I formatted the entire cell.

I add a file that serves as an example.

Thank you very much for your help.

JuanM.

**Domski** · 12-14-2010, 10:58 AM

I'm afraid you can't format part of the results of a formula different to the rest.

Dom

**jmaterano** · 12-14-2010, 11:06 AM

Through VBA you can not or using another technique?

Thanks

Regards,

**Domski** · 12-14-2010, 11:43 AM

Try this:

Sub test()

    Dim lngLoopRow As Long
    Dim lngLastRow As Long
    
    lngLastRow = Cells(Rows.Count, 2).End(xlUp).Row
    
    For lngLoopRow = 3 To lngLastRow
    
        With Range("F" & lngLoopRow)
        
            .Value = Range("B" & lngLoopRow) & " " & Range("C" & lngLoopRow) & " tiene " & _
                Range("D" & lngLoopRow) & " años y su profesión es " & Range("E" & lngLoopRow)

            With .Characters(Start:=1, Length:=Len(Range("B" & lngLoopRow) & " " & Range("C" & lngLoopRow))).Font
                .FontStyle = "Bold"
            End With

            With .Characters(Start:=Len(.Value) - Len(Range("E" & lngLoopRow)) + 1, Length:=Len(Range("E" & lngLoopRow))).Font
                .FontStyle = "Italic"
            End With
            
        End With

    Next lngLoopRow

End Sub

Dom

**jmaterano** · 12-14-2010, 01:04 PM

Dom hello thank you very much for the reply.

The code works very well, there would be some way that will not erase the arguments of the concatenate function?

Add the file to see that when I run the macro deletes the function arguments.

Thank you very much for your help.

Greetings

**Domski** · 12-14-2010, 02:37 PM

No, the only way is to do the whole thing including the concatenation using VBA.

As I said it's not possible to format the result of a formula differently, you can only do this with fixed text strings.

Dom