conditionally Shrink to fit or Wrap text with certain font size using byval

[FinallyLearning]

Hi, everyone. I am grateful for all the knowledge shared on here. This board is probably increasing our productivity quite a bit across the world.

I have a task - I need to generate, on demand, a little glass plaque of sort with key things written on it to commemorate a closed contract/deal.
It needs to show the company names as well as other details.
In that glass plaque, I need the company names to dynamically change font size so it can be aesthetically pleasing to then move to a powerpoint.
I have been fiddling with the below, but I can't quite figure out how to make this work.

When I change anything (doesn't have to be Range 86), I want all values at row 86 to run a calculation on its length, and if it is above 15 letters, I want to to be given a specific font size, and wrap the text to the cell, otherwise shrink to fit to the cell. Could you advise what I am doing wrong? Thank you all!

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
   Dim cCell As Range
   Dim c As Integer

    If Not Intersect(Range("86:86"), Target) Is Nothing Then

    For c = 1 To 500
    Set cCell = Range("86" & c)
        With cCell
          If Len(.Value) > 15 Then
                .Font.Size = 26
                .WrapText = True
            Else
                .ShrinkToFit = True
        End With
        Next r
  End If
 Application.EnableEvents = True
End Sub

 

[RoryA]

Welcome to the forum

At a guess, you meant for this:

Set cCell = Range("86" & c)

to be:

Set cCell = Cells(86, c)