Excel VBA

Change Case to Mixed

There is a routine that adds a space after MC and changes an O' to O’. The first makes the name look correct and the second is just my housekeeping. If the space is not added, MCCULLOUGH becomes Mccullough; with this Mc Cullough. I would like to fix the DEs but there is no hard fast rule.

Sub ChangeCase()
    Dim MemberCell As Range	'Housekeeping to establish variables
    Dim BottomRow As Integer
    Dim k As Integer
    
    Windows(ThisNewSheet).Activate
    Application.StatusBar = "Changing case..."	'Status Bar
    Range("A2").Select
        BottomRow = ActiveCell.SpecialCells(xlLastCell).Row 'Where to look
        Cells.Replace What:="MC", Replacement:="MC ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False
        Cells.Replace What:="'", Replacement:="’", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False
		'Clean up MC and O' before changing Case
            For k = 2 To BottomRow
                Cells(k, 1).Value = Application.Proper(Cells(k, 1).Value)
            Next
    Range("A2").Select
        '----- reserve for putting back special cases
    ActiveCell.Offset(1, 0).Select
End Sub