Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 299
  • Last Modified:

VBA to change font size for cell with large text

Hi guys,

The following code works for a cell
If Len([C13]) > 27 Then [C13].Font.Size = 8 Else [C13].Font.Size = 9

however I need to cover a worksheet Range(C13:U500)

Please send the correct code.

Thank you,

Robert
0
rsen1
Asked:
rsen1
  • 4
  • 3
1 Solution
 
dlmilleCommented:
Assuming you want to do this test on every cell in the range(C13:U500):

Sub changeFont()
Dim r As Range
Dim rng As Range

    Set rng = Range("C13:U500")
    
    For Each r In rng
        If Len(r.Value) > 27 Then
            r.Font.Size = 8
        Else
            r.Font.Size = 9
        End If
    Next r
End Sub

Open in new window



Dave
0
 
rsen1Author Commented:
Thank you Dave, works great
0
 
rsen1Author Commented:
Dave it works great however it is very slow to calculate any suggestions? Thank you
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
dlmilleCommented:
Sub changeFontV2()
Dim r As Range
Dim rng As Range

    Set rng = Range("C13:U500")
    
    rng.Font.Size = 9
    For Each r In rng
        If Len(r.Value) > 27 Then
            r.Font.Size = 8
        End If
    Next r
End Sub

Open in new window


Dave
0
 
dlmilleCommented:
And then, try this:

Sub changeFontV3()
Dim r As Range
Dim rng As Range
Dim rChange As Range

    Set rng = Range("C13:U500")
    
    rng.Font.Size = 9
    For Each r In rng
        If Len(r.Value) > 27 Then
            If rChange Is Nothing Then
                Set rChange = r
            Else
                Set rChange = Union(rChange, r)
        End If
    Next r
    rChange.Font.Size = 8
End Sub

Open in new window


Are any of these faster?

Dave
0
 
rsen1Author Commented:
Yes, thank you, the last one is great
0
 
dlmilleCommented:
Unless it gets VERY VERY large, the Union should be a pretty fast player.  I sometimes do that to "mark" ranges that I want to do something with like delete, or whatever - and it occurred to me it might speed you up with font changes.

Take care,

Dave
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now