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

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

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.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,448
Messages
5,642,218
Members
417,262
Latest member
andrewd1

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