MrExcel Publishing
Your One Stop for Excel Tips & Solutions

How to do conditional formatting with different font size


Posted by Gary on December 18, 2000 10:26 AM

I try to transfer data to other sheet (for a report). But I want to do a conditional formatting
if the len() is less than or equal to 6 characters - use font size 10
if the len() is equal to or more than 7 characters - use font size 7
Because I try to fit all character in the cell.
I already try Shrink to fit, but doesn't work in some case.

Please help! Thanks.



Posted by Celia on December 21, 2000 11:10 PM


Gary
I can't think of a way of doing this without a macro.
Try the following macro. Before running it, you need to select the cells that you want to format.

Sub Set_Font_Size()
Dim rng As Range, cell As Range
Set rng = Intersect(Selection, ActiveSheet.UsedRange)
For Each cell In rng
If Len(cell) <= 6 Then
cell.Font.Size = 10
Else
cell.Font.Size = 7
End If
Next
End Sub

Celia