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

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,559
Messages
6,114,302
Members
448,564
Latest member
ED38

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
Back
Top