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

 

[NdNoviceHlp]

Not real clear where your data is or what U actually want to change. Trial this. HTH. Dave

Function ChangeFont(myRange As Range, srchWord As String, FontName As String, FontSize As Integer)
'myrange is range to be changed, srchword is letter/word to be changed
'fontname is type of font, fontsize is size of font
Dim startChar As Integer, _
    lenColor As Integer, _
    nxtWord As Integer
Dim w As Range
Dim firstAddress As String
      With myRange
        Set w = .Find(srchWord, lookat:=xlPart, MatchCase:=True)
         If Not w Is Nothing Then
           firstAddress = w.Address
          Do
           startChar = InStr(1, w, srchWord)
              lenColor = Len(srchWord)
             With w.Characters(Start:=startChar, Length:=lenColor)
              .Font.Name = FontName
              .Font.Size = FontSize
             End With
              Set w = .FindNext(w)
          Loop While Not w Is Nothing And w.Address <> firstAddress
         End If
      End With
End Function

To operate...

Dim Myrng As Range
Set Myrng = Sheets("Sheet1").Range("j1:j10")
Call ChangeFont(Myrng, "N", "ariel", 128)
Call ChangeFont(Myrng, "ovember", "ariel", 20)

This will Change "N" to arial 128 and "ovember" to ariel 20 in sheet1 J1:J10. Adjust to suit. The letters/words entered to be changed are case sensitive.

 

[Fluff]

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

 

[mingandmong]

Hi Dave i could not get this to run i copied both code into 2 modules and the macro did not run
i have posted again to try and make my request more clear

 

[mingandmong]

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

 

[mingandmong]

Hopefully this will be clearer im using excel 2010

I paste data into column "A" from another sheet
Row 1 is the current working data
Row 2 is modified i have now additional data in "F2" its a vlookup formula returning the value "November" and displaying it in column "J2" howver as you can see the font is incorrect
so i needed the letter N in Arail 128 and November in Arail 20 all in the same cell
Row 3 is the expected result but im using an extra Row 4 to display the word November
By ausing an extra row or merging the cells i cannot then paste the origanal data into column A due to cells merged
Another solution if a could copy & paste my data in Column A as a paste miss a row paste miss a row paste miss a row ect


Column "A" data below inc formulas
03A030
01B740
05A010

B2	=TRIM(A2)
C2	=LEFT(B2,2)
D2	=RIGHT(B2,4)
E2	=IFERROR(VLOOKUP(B2,Last!$A:$D,4,0),"")
H2	=C2
I2	=D2
J2	=E2
B3	=TRIM(A3)
C3	=LEFT(B3,2)
D3	=RIGHT(B3,4)
E3	=IFERROR(VLOOKUP(B3,Last!$A:$D,4,0),"")
F3	=IFERROR(VLOOKUP(B3,Last!$A:$E,5,0),"")
H3	=C3
I3	=D3
J3	=CONCATENATE(E3,G3,F3)
B4	=TRIM(A4)
C4	=LEFT(B4,2)
D4	=RIGHT(B4,4)
E4	=IFERROR(VLOOKUP(B4,Last!$A:$D,4,0),"")
F4	=IFERROR(VLOOKUP(B4,Last!$A:$E,5,0),"")
H4	=C4
I4	=D4
B5	=TRIM(A5)
C5	=LEFT(B5,2)
D5	=RIGHT(B5,4)
E5	=IFERROR(VLOOKUP(B5,Last!$A:$D,4,0),"")
H5	=C5

<tbody>
</tbody>

areaT

  
ABCDEFGHIJ103A03003A03003A030K 
 
03A030K201B74001B74001B740NNovember 
01B740N November305A01005A01005A010CCharlie 
05A010C4 
 
 
 
 
 
 
 
Charlie
Spreadsheet FormulasCellFormulaB1=TRIM(A1)C1=LEFT(B1,2)D1=RIGHT(B1,4)E1=IFERROR(VLOOKUP(B1,Last!$A:$D,4,0),"")H1=C1I1=D1J1=E1B2=TRIM(A2)C2=LEFT(B2,2)D2=RIGHT(B2,4)E2=IFERROR(VLOOKUP(B2,Last!$A:$D,4,0),"")F2=IFERROR(VLOOKUP(B2,Last!$A:$E,5,0),"")H2=C2I2=D2J2=CONCATENATE(E2,G2,F2)B3=TRIM(A3)C3=LEFT(B3,2)D3=RIGHT(B3,4)E3=IFERROR(VLOOKUP(B3,Last!$A:$D,4,0),"")F3=IFERROR(VLOOKUP(B3,Last!$A:$E,5,0),"")H3=C3I3=D3B4=TRIM(A4)C4=LEFT(B4,2)D4=RIGHT(B4,4)E4=IFERROR(VLOOKUP(B4,Last!$A:$D,4,0),"")H4=C4 
[url=http://www.excel-jeanie-html.de/index.php?f=1]Excel tables to the web - Excel Jeanie Html 4[/url]

 

[NdNoviceHlp]

Hi mingandmong not sure why my code did't work for U unless the merge thing is doing something? Did U adjust the sheet Name and the call function is case specific. So...

Dim Myrng As Range
Set Myrng = Sheets("YourSheetName").Range("j1:j10")
Call ChangeFont(Myrng, "N", "ariel", 128)
Call ChangeFont(Myrng, "ovember", "ariel", 20)

Also, adjust the range to suit.
To get Fluff's code to not delete formulas. Just remove this line...

.Value = .Value

However, Fluff's code changes all values in J2:J& whatever not just the November.
Dave

 

[mingandmong]

[CODEareaT

	A	B	C	D	E	F	G	H	I	J

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:186px;"><col style="width:72px;"><col style="width:34px;"><col style="width:50px;"><col style="width:26px;"><col style="width:57px;"><col style="width:16px;"><col style="width:64px;"><col style="width:412px;"><col style="width:152px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ccffff]#ccffff[/URL] , align: center"]03A030[/TD]
[TD="align: center"]03A030[/TD]
[TD="align: center"]03[/TD]
[TD="align: center"]A030[/TD]
[TD="align: center"]K[/TD]

[TD="align: center"]
[/TD]
[TD="align: center"]03[/TD]
[TD="align: center"]A030[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=333333]#333333[/URL] , align: center"]K[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ccffff]#ccffff[/URL] , align: center"]01B740[/TD]
[TD="align: center"]01B740[/TD]
[TD="align: center"]01[/TD]
[TD="align: center"]B740[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]November[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]01[/TD]
[TD="align: center"]B740[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=333333]#333333[/URL] , align: center"]N November[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ccffff]#ccffff[/URL] , align: center"]05A010[/TD]
[TD="align: center"]05A010[/TD]
[TD="align: center"]05[/TD]
[TD="align: center"]A010[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]Charlie[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]05[/TD]
[TD="align: center"]A010[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=333333]#333333[/URL] , align: center"]C[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ccffff]#ccffff[/URL] "]
[/TD]

[TD="align: center"]
[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=333333]#333333[/URL] , align: center"]Charlie[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula B1 =TRIM(A1) C1 =LEFT(B1,2) D1 =RIGHT(B1,4) E1 =IFERROR(VLOOKUP(B1,Last!$A:$D,4,0),"") H1 =C1 I1 =D1 J1 =E1 B2 =TRIM(A2) C2 =LEFT(B2,2) D2 =RIGHT(B2,4) E2 =IFERROR(VLOOKUP(B2,Last!$A:$D,4,0),"") F2 =IFERROR(VLOOKUP(B2,Last!$A:$E,5,0),"") H2 =C2 I2 =D2 J2 =CONCATENATE(E2,G2,F2) B3 =TRIM(A3) C3 =LEFT(B3,2) D3 =RIGHT(B3,4) E3 =IFERROR(VLOOKUP(B3,Last!$A:$D,4,0),"") F3 =IFERROR(VLOOKUP(B3,Last!$A:$E,5,0),"") H3 =C3 I3 =D3 B4 =TRIM(A4) C4 =LEFT(B4,2) D4 =RIGHT(B4,4) E4 =IFERROR(VLOOKUP(B4,Last!$A:$D,4,0),"") H4 =C4 <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web - Excel Jeanie Html 4][/CODE]

 

[mingandmong]

Hi Dave i did not change the sheet name... i will do so now and give it a go
i will also look at fluffs code and also try that

 

[NdNoviceHlp]

I should have maybe included how to operate the Call function...

Sub Test
Dim Myrng As Range
Set Myrng = Sheets("YourSheetName").Range("j1:j10")
Call ChangeFont(Myrng, "N", "ariel", 128)
Call ChangeFont(Myrng, "ovember", "ariel", 20)
End Sub

Then run the sub. Dave