Multiple font sizes in a single cell

[Pete]

Hi everyone.

OK, so I know you can set and get the font size of a cell in Excel by using
Cell.Font.Size, but if there are multiple font sizes (some of the text in
the cell at 12pt and some at 10pt in the same cell), Cell.Font.Size returns
a Null value. Is there anyway to get the different font sizes out of a
single cell?

TIA for any help.
-Pete

 

[L. Howard Kittle]

Hi Pete,

Here's a stab at it but it may not be what you are looking for. You
probably want the code to spit out the different font sizes. With these two
subs you have to set the Start and Length components

Where F2 has seven characters and the first 3 are one font size and the next
four are another.

Sub FontSize()
Dim i As Integer
Dim j As Integer
Range("F2").Select
i = ActiveCell.Characters(Start:=1, Length:=3).Font.Size
j = ActiveCell.Characters(Start:=4, Length:=4).Font.Size
MsgBox i & " " & j
End Sub

With this sub you set the Start and Length in cells F1 & G1.

Sub FontSizeToo()
Dim i As Integer
Dim s As Integer
Dim l As Integer

s = Range("F1").Value
l = Range("G1").Value

Range("F2").Select
i = ActiveCell.Characters(Start:=s, Length:=l).Font.Size
MsgBox i
End Sub

HTH
Regards,
Howard

 

[Pete]

Thanks Howard,

That at least sends me in the right direction. However, I won't know the
start and end points where the font size changes, so I'll probably have to
do some kind of Do Until loop on the characters to determine the different
start and end positions for the different font sizes.

Thanks for your help!

-Pete

 

[L. Howard Kittle]

That's what I thought, and I did kick around trying to count how many of a
particular font size was in a cell, and it worked, but again, had to set the
Start and Length. If you could get past that I think your home free.

Good luck,
Regards,
Howard