Change cell font size based on formula

[ColinCoady]

I'm look for VBA code to change the font size in a cell based on the number of lines in another cell.
I use the following formula to find out the number of lines in the other cell:
=LEN(InfoEntry!$B$14)-LEN(SUBSTITUTE(InfoEntry!$B$14,CHAR(10),""))+1

If the result of this formula is "8" lines or less the the font size for cell TenantQuote!$A$15 needs to be 12, if 9-14 lines the font size 11 is needed and greater than 14 lines then font size 10 is needed

Can anyone help me with this?
-Colin

 

[Scott T]

You could do this with Conditional Formatting no need for VBA.

 

[ColinCoady]

You can't change font size with CF

 

[Scott T]

I am assuming you want this to run on change of the cell so this is a worksheet change code that would go into the infoentry sheet

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B14")) Is Nothing Then
l = Len(Sheets("InfoEntry").Range("$B$14")) - Len(Replace(Sheets("infoentry").Range("$B$14"), vbLf, "")) + 1
Select Case l
    Case Is <= 8
        Sheets("tenantquote").Range("A15").Font.Size = 12
    Case 9 To 14
        Sheets("tenantquote").Range("A15").Font.Size = 11
    Case Else
        Sheets("tenantquote").Range("A15").Font.Size = 10
End Select

End If
End Sub

 

[ColinCoady]

Looks like that should work but it has no effect.
After testing original I edited your code on the sheet names - ie "tenantquote" to TenantQuote" Wasn't sure if it was case sensitive or not. I'm very new at this.
No affect so i also changed ranges thinking merged cells needed the full address ie $B$14 changed to $B$14:$F$14 but again no effect.

I change the data so the number of lines it calculates change but no effect. The font size remains the same.

Any more thoughts.
Can I send you the workbook?

 

[Scott T]

Where did you put the code?
Right click on the InfoEntry and select view code. That is where it should be.

Note that if you have a formula in B14 the recalculation of the formula will not trigger the event.

 

[ColinCoady]

I put the code in the correct place and then got a Compile error: Ambiguous name detected: Worksheet_Change

 

[KOKOSEK]

You probably have two Worksheet_Change subs.

 

[ColinCoady]

You were correct. I combined it with the other one and it's working like a charm.
Thank you Scott T
Great job.
-Colin

 

[ColinCoady]

Thank you as well KOKOSEK
-Colin