variable fonts within a single cell

Macintosh

New Member
Joined
Jan 21, 2005
Messages
2
I am trying to automate the process of changing the font size within a single cell, in this case the cell is D2 which contains the formula:

=A2&" "&B2&"."&C2

the visual result in D2 looks like this £1.99

the value in A2 is £
the value in B2 is 1
and the value in C2 is 99

however I need the £ sign to be 8 point text, the 1 needs to be 12 point, and the 99 needs to be 8 point.

Can this be done automatically through a formula or through a macro?[/b]
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
You can't apply Rich Text Formatting to the result of a formula.

So here is some code:

Code:
Sub Test()
    With Range("D2")
        .Value = [A2] & " " & [B2] & "." & [C2]
        .Characters(1).Font.Size = 8
        .Characters(3, Len([B2])).Font.Size = 12
        .Characters(3 + Len([B2]) + 1, Len([C2])).Font.Size = 8
    End With
End Sub
 

Macintosh

New Member
Joined
Jan 21, 2005
Messages
2
Thanks for the code, do I insert this into the formula bar or elsewhere, I cannot seem to make it work, sorry in advance for my ignorance
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Press Alt+F11 to go to the Visual Basic Editor. Click your workbook in the Project window and choose Insert|Module from the menu. Paste the code in the window on the right. Press Alt+F11 again to return to your worksheet.

To run the code choose Tools|Macro|Macros, click Test then OK.

The code will only work for cell D2 on the active sheet. It will need changing if it needs to be generic.

It would be nice if all this could be done with a VBA custom function called from a worksheet. But I'm afraid that's not possible.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,709
Messages
5,855,262
Members
431,717
Latest member
Ibyb

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top