TEXT formatting withing single quote

Try this (untested)

Option Explicit
Sub formatting()
Dim c As Range, n As Long, SplitStr As Variant
For Each c In Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row)
    SplitStr = Split(c.Value, "'")
    c.Value = IIf(UBound(SplitStr) Mod 2 = 1, "''", "") & c
    For n = 0 To UBound(SplitStr)
        If n Mod 2 <> (UBound(SplitStr) Mod 2) Then
            On Error Resume Next
            c.Replace "'" & SplitStr(n) & "'", IIf(n = 0, "", "'") & WorksheetFunction.Proper(SplitStr(n)) & "'"
        End If
    Next
        c.Value = UCase(Left(c, 1)) & Right(c, Len(c.Offset(, 1) - 1))
Next
End Sub