Macro to convert all Letters in bold to Capitals

[tonywatsonhelp]

Hi Everyone,
I'd like a macro that when run converts all letters that are bold to capitals in range AZ2:AZ2000?

So what I want to do is highlight certain letters only and have them Capitalise,

please help if you can

Thanks

Tony

 

[JGordon11]

Function TextCapitalize(txt, n)
    Dim i
    n = n
    If IsArray(n) Then
        For Each i In n
            If i = 0 Then
                TextCapitalize = txt
                Exit Function
            End If
            txt = TC(txt, i)
        Next
    Else
        txt = TC(txt, n)
    End If
    TextCapitalize = txt
End Function

Function TC(txt, n) As String
    If n = 0 Then
        TC = txt
    Else
        TC = Left(txt, n - 1) & UCase(Mid(txt, n, 1)) & Right(txt, Len(txt) - n)
    End If
End Function

Function BoldArray(cl As Range) As Variant
    Dim i As Long, CB As String
    For i = 1 To Len(cl)
        If cl.Characters(i, 1).Font.FontStyle = "Bold" Then
           CB = CB & i & ","
        End If
    Next
    If Len(CB) = 0 Then
        BoldArray = 0
    Else
        BoldArray = Split(Left(CB, Len(CB) - 1), ",")
    End If
End Function

Sub CapBold()
    Dim s, i As Long, ubs As Long, rng As Range, CharactersChecked As Long, ls As Long, t As Double
    t = Timer
    Set rng = Range("AZ2:AZ2000")
    s = rng
    ubs = UBound(s)
    With rng
        For i = 1 To ubs
            ls = Len(s(i, 1))
            CharactersChecked = CharactersChecked + ls
            If ls Then s(i, 1) = TextCapitalize(s(i, 1), BoldArray(.Item(i, 1)))
        Next
        .Value = s
        .Font.Bold = False
    End With
    Debug.Print CharactersChecked & " characters in " & Timer - t & " seconds"
End Sub

 

[tonywatsonhelp]

Thank you Jgordon11,
this looks great.
Big help
Thanks you very much

Tony