Conditional Formatting by character count

[rjwell]

If this topic is covered by a previous thread ... please forgive and link me to the thread.

I've been searching for code that can do the following ...

After cell update
If character count is 5 or less set font size equal to 12
If character count greater than 5 set font size equal to 10

I'm not into coding and any of my VBA attempts results in no change.

Thanks for the help.

 

[Georgiboy]

Welcome, right click on the sheet tab you want the code to run in and select view code. Then paste this in...

Private Sub Worksheet_Change(ByVal Target As Range)

Select Case Len(Target.Value)
    Case Is <= 5: Target.Font.Size = 12
    Case Else: Target.Font.Size = 10
End Select

End Sub

Hope this helps

 

[rjwell]

Thanks Georgiboy,

One problem (I think) I don't want it applied to every cell on the sheet, which your solution would do, right?

How can I apply that to just cell B43 (for example).

Thanks for your help.

 

[Georgiboy]

For a single cell you could use...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("B43")) Is Nothing Then
    Select Case Len(Target.Value)
        Case Is <= 5: Target.Font.Size = 12
        Case Else: Target.Font.Size = 10
    End Select
    
End If
End Sub

For more than one single cell you could use...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("B43, B45, B47")) Is Nothing Then
    Select Case Len(Target.Value)
        Case Is <= 5: Target.Font.Size = 12
        Case Else: Target.Font.Size = 10
    End Select
    
End If
End Sub

Hope this helps

 

[rjwell]

Sweeeeeet.

Thanks

 

[heidist]

For a single cell you could use...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("B43")) Is Nothing Then
    Select Case Len(Target.Value)
        Case Is <= 5: Target.Font.Size = 12
        Case Else: Target.Font.Size = 10
    End Select
    
End If
End Sub

For more than one single cell you could use...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("B43, B45, B47")) Is Nothing Then
    Select Case Len(Target.Value)
        Case Is <= 5: Target.Font.Size = 12
        Case Else: Target.Font.Size = 10
    End Select
    
End If
End Sub

Hope this helps

I was hoping you could help me out, I have a similar situation, only I want to apply a red background to any cell with more than 60 characters. And I need to apply this to an entire column (almost 10,000 rows of data).

If it has less than 60 characters it doesn't have to do anything. I've tried to do this through Conditional Formatting (there's another thread about using "=LEN($D4)>60") but it didn't work, some cells with only 23 characters got the red background, and some cells with 63 characters didn't get the red background. Any ideas?

Thank you so much!

 

[Gerald Higgins]

Hi. I can't replicate this.

I used

=LEN(D4)>60

and it seems to work fine.

Can you give an example of one of the text strings that gives you the wrong result ?

 

[heidist]

the following two both had the red background:
C Welding Fabrication
D Hospitality and Tourism Management- Intern Travel and Tourism
and then right below the D Hospitality one was an idea course name that WASN'T red.

The Welding name shouldn't have turned red, and the second Hospitality program should have.

 

[schielrn]

Maybe try:

=LEN(trim(D4))>60

Also make sure there are no quotes in teh formula in conditional formatting.

Hope that helps.

 

[heidist]

This did the same thing, Welding became red, and the second Hospitality did not.