How do I "conditionally" change font size based on text length?

[Guest]

A cell of specific size gets a text string of variable length from another
sheet in the workbook. I would like to make the font size conditional based
on the number of characters changes (i.e., more characters - smaller font).
Conditional Formatting does not address font size.

 

[Tom Ogilvy]

Private Sub worksheet_Calculate()
Dim cell as Range, l as Long
set cell = Me.Range("B9")
l = len(cell.text)
if l < 20 then
cell.Font.Size = 12
elseif l < 50 then
cell.font.Size = 10
elseif l < 100 then
cell.Font.Size = 9
Else
cell.Font.Size = 8
end if
End sub

Right click the sheet tab where you want this and select view code. Put in
code like the above.

 

[Dave Peterson]

You may want to try:
Format|cells|Alignment tab|text control section|check shrink to fit

Not quite the same, but maybe...