2 sperate Font sizes in the same cell

[mingandmong]

Hi Im using excel 2010

can anyone help me in in creating 2 sepertate size fonts in the same cell the first font is size 128 Arial & the second font size is 20 Arial

the formulas are in coulmn "E" & "F" (Vlookups from another sheet), i concatanate them with a space inbetween and word wrap so that the letter N is above and November is below in the

same cell which is in column"J"

i can manualy accomplish this by in the task bar highlighting the N and then Novemeber and manualy change the font size

however i have a lot of data in "E" & "F" and the data changes on a daily basis

any workaround is apprecied by either changing concatanate to another formula or a macro or vba

Many thanks

 

[mingandmong]

Hi Dave changed the sheet name, and still can not run as a macro.. i open up the macro and its empty?
also the code being specific would need to change November was an example the other rows below are charlie, foxtrot, yankee zulu ect..ect

 

[Fluff]

Hi Fluff thankyou for your hard work.. the result is what i wanted, but it deletes my formula's in column j so i cannot use the same rows again
i will post another example there may be annother way to get my desired results thanks once again

AFAIK there is no way of doing what you want if the cell contains a formula. Which is why I converted the formula into values.

 

[mingandmong]

Thank-you Fluff for all your help,
Its working fine... i just copy and paste column "J" with the origanal formula's via another macro after every time i run your macro

 

[Fluff]

Glad we could help & thanks for the feedback

 

[FracinDean]

You cannot do that via a formula, but this macro should do what you want

Sub FontSize()
   
   Dim Cl As Range
   
   For Each Cl In Range("J2", Range("J" & Rows.Count).End(xlUp))
      With Cl
         .Value = .Value
         .Font.Name = "Arial"
         .Font.size = 20
      .Characters(1, 1).Font.size = 128
      End With
   Next Cl
   
End Sub

Thank you for this very useful code Fluff. I have always wanted to be able to change the font of part of a cell's contents using code, and the .Characters attribute is the magic piece I needed. Below is an example where I used it to subscript part of a cell. I think I will be able to generalize it by making the starting cell and ending cell parameters in the function, and maybe also using a parameter for what I want to change (subscript or superscript etc.0. Very helpful!

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Dim Cl As Range
For Each Cl In Range("rType")
With Cl
   .Characters(13, 17).Font.Subscript = True
End With
Next Cl
[/FONT]

 

[Fluff]

Glad it helped you & thanks for the feedback