I am trying to use VBA to change the formatting of a worksheet. I need to do 2 things:

1. Change the font when a cell has a large amount of text
2. Change the font when a cell contains a bullet character: " • "

I've been able to accomplish 1 below, but can't figure out 2:

Sub FormatText()

    Dim xCell As Range
    For Each xCell In Sheets("Output").Range("A16:A64")
        With xCell
            If Len(.Text) > 100 Then
                .Font.Name = "Arial Medium"
            Else
                .Font.Name = "Calibri Bold"
            End If
        End With
    Next
End Sub

CodePudding user response：

You can just add that condition to your If statement:

Sub FormatText()
    Dim xCell As Range
    For Each xCell In WorkSheets("Output").Range("A16:A64")
        With xCell
            If Len(.Text) > 100 Or Instr(.Text, Chr$(149)) > 0 Then
                .Font.Name = "Arial Medium"
            Else
                .Font.Name = "Calibri Bold"
            End If
        End With
    Next
End Sub

If you need separate If statements:

Sub FormatText()
    Dim xCell As Range
    For Each xCell In WorkSheets("Output").Range("A16:A64")
        With xCell

            If Len(.Text) > 100 Then
                .Font.Name = "Arial Medium"
            Else
                .Font.Name = "Calibri Bold"
            End If

            If Instr(.Text, Chr$(149)) > 0 Then
                .Font.Name = "Arial Medium"
            Else
                .Font.Name = "Calibri Bold"
            End If

        End With
    Next
End Sub