2 sperate Font sizes in the same cell

M

mingandmong

Active Member
Joined
Oct 15, 2014
Messages
339
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
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Sort by date Sort by votes
Not real clear where your data is or what U actually want to change. Trial this. HTH. Dave
Code: 
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...
Code: 
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.
 
Upvote 0
You cannot do that via a formula, but this macro should do what you want
Code: 
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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>
HTML: 
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]
 
Upvote 0
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...
Code: 
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...
Code: 
.Value = .Value
However, Fluff's code changes all values in J2:J& whatever not just the November.
Dave
 
Upvote 0
[CODEareaT


ABCDEFGHIJ





<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
CellFormula
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]
 
Upvote 0
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
 
Upvote 0
I should have maybe included how to operate the Call function...
Code: 
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
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

O
  • Solved
Index/Match, Nested IF statements, or Array Formula?
Replies
9
Views
166
Ol Reliable
O
hardeep.kanwar
  • Question Question
Paste data based on cell color
Replies
2
Views
62
Peter_SSs
Peter_SSs
P
  • Question Question
VBA to identify today's column range then apply font colour change to any cells if their equivalent in another column is of a certain value.
Replies
5
Views
708
mumps
mumps
N
  • Solved
Copy specific cells based on controls checkbox and paste in the next empty row on a table
Replies
5
Views
81
nstaab07
N
G
  • Question Question
Trouble getting lows and highs data analyzed and marked with my critera requirements
Replies
7
Views
677
I_know_nuffin
I

Forum statistics

Threads
1,224,823
Messages
6,181,171
Members
453,021
Latest member
Justyna P

Share this page

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