Bold in Excel

[Melanie]

Hi,

In Excel, is it possible to bold only a part of a sentence? I mean, I know it's possible when in the cell there's NO =

="Suite à cette analyse, nous sommes d'opinion que la "&'Saisie rapport'!B20&" de cette propriété, en date du "&CTXT(JOUR(('Saisie rapport'!B5));0;VRAI)&" "&RECHERCHEV(MOIS(('Saisie rapport'!B5));Feuil1!K1:Feuil1!L12;2)&" "&CTXT(ANNEE(('Saisie rapport'!B5));0;VRAI)&" est de "&Feuil1!H28&" $. « "&Feuil1!K34&" »"

This is a big sentence in one of the cell. But the problem is that I want to bold or underline just a part of the cell. It is suppose to give something like this :

Suite à cette analyse, nous sommes d'opinion que la VALEUR MARCHANDE PROBABLE de cette propriété, en date du 12 octobre 2001 est de 330500 $. « TROIS CENT TRENTE MILLE CINQ CENTS »

I want to bold only the 12 octobre 2001. Is it possible in VBA? How?

Thanks

[WorkHorse]

You will need to use the Characters property. The VBA recorder doesn't seem to like recoding changes to this property. You will need to pass the Start and Length of the Characters that you want formatted. So, you might want to assign the first part of your string to a variable so that you can check it's length to know where to start formatting:

Dim sMyDate As String
Dim sMyText As String
 
sMyDate = "12 octobre 2001"
sMyText = "Suite à cette analyse, nous etc... "
 
With Worksheets("Sheet1").Range("A12")
    .Value = sMyText & sMyDate & " blah blah blah"
    .Characters(Len(sMyText) + 1, Len(sMyDate)).Font.Bold = True
End With

[Melanie]

Hi,

Ok, what about if I want to put that in bold? Is it possible? : "&RECHERCHEV(MOIS(('Saisie rapport'!B5));Feuil1!K1:Feuil1!L12;2)&" "&CTXT(ANNEE(('Saisie rapport'!B5));0;VRAI)&" (it gaves 12 octobre 2001)...

Thanks!

Mel

[WorkHorse]

If the cell contains a formula, Excel changes the entire cell font to match the font of the first character. I haven't tried working with this before, but Excel seems to be pretty stubborn about it. I couldn't find any way to force it to accept multiple fonts in a cell with a formula.

The easiest workaround would probably be to put your formula in a hidden cell (see Excel help Hide all values in a cell). Then set up a change or calculate event that runs a sub whenever the cell value changes. Assign the cell value to a variable, use InStr and Len to find the start and length of the text that needs to be formatted, put the variable into the displayed cell then change the font like in the above code.

I imagine that's a bit more complex than what you were looking for, but I can't think of something simpler. I think it would be pretty slick though.

Post back if you need help coding the workaround or if you find a better way.