All text lowercase before a specific character

[Brian F]

Excel 2010, Windows 8: I have this code that changes all of the text in a range to Not Bold, then goes through the range line by line and changes everything before a colon to Bold (a colon appears in each statement). At the present time each word before the colon begins with a Capital letter. I would now like to make everything before the colon lowercase . Can someone help me with this?

Sub Bold_To_Colon()

'Makes everything NOT bold then,
'Makes all the text before the colon bold

Dim c As Range, x As Long

Range("AS18:CC617").Select
Selection.Font.Bold = False

For Each c In Range("AS18:AS" & Range("AS" & Rows.Count).End(xlUp).row)
x = InStr(c, ":")
c.Font.Bold = False
If x > 0 Then c.Characters(1, x).Font.Bold = True
Next
End Sub

 

[shg]

Untested:

Sub BoldToColon()
    Dim cell        As Range
    Dim i           As Long

    Range("AS18:CC617").Font.Bold = False

    With Range("AS18", Cells(Rows.Count, "AS"))
        .Font.Bold = False

        For Each cell In .Cells
            i = InStr(cell.Value, ":")

            If i Then
                cell.Value = LCase(Left(cell.Value, i)) & Mid(cell.Value, i + 1)
                cell.Characters(1, i).Font.Bold = True
            End If
        Next cell
    End With
End Sub

 

[Brian F]

That works perfectly! Thank you so much shg. I now need to make the fist letter of each string a CAP. Do you have the time to show me how?

 

[shg]

Still untested:

Sub BoldToColon()
    Dim cell        As Range
    Dim i           As Long
    Dim s           As String

    Range("AS18:CC617").Font.Bold = False

    With Range("AS18", Cells(Rows.Count, "AS"))
        .Font.Bold = False

        For Each cell In .Cells
            s = cell.Value
            i = InStr(s, ":")

            If i Then
                s = UCase(Left(cell.Value, 1)) & _
                    LCase(Mid(s, 2, i - 1)) & _
                    Mid(cell.Value, i + 1)
                cell.Value = s
                cell.Characters(1, i).Font.Bold = True
            End If
        Next cell
    End With
End Sub

 

[Brian F]

Again, that works perfectly. While I was waiting I figured out a solution for myself but yours is vastly more elegant. Thank you for your help. That wraps up a project for me.

 

[shg]

You're welcome.

 

[shg]

Clenaup:

Sub BoldToColon()
    Dim cell        As Range
    Dim i           As Long
    Dim s           As String

    Range("AS18:CC617").Font.Bold = False

    With Range("AS18", Cells(Rows.Count, "AS"))
        .Font.Bold = False

        For Each cell In .Cells
            s = cell.Value
            i = InStr(s, ":")

            If i Then
                s = UCase(Left(s, 1)) & _
                    LCase(Mid(s, 2, i - 1)) & _
                    Mid(s, i + 1)
                cell.Value = s
                cell.Characters(1, i).Font.Bold = True
            End If
        Next cell
    End With
End Sub