Proper application not handling apostrophes

[nickmax1]

I have a macro that changes my text to Proper but it cant handles apostrophes, so for example

PETER'S HOUSE

Shoule become Peter's House, but instead the macro changes it to Peter'S House

How can i amend this?

For Each rArea In .Range("Account,AccountAPRP").Areas
         rArea.Value = Application.Proper(rArea.Value)
      Next rArea

For your info the full macro is below

Sub FormattingGlobal()
   Dim lCalcMode              As Long
   Dim vData
   Dim rArea                  As Range
   Dim x                      As Long
   Dim y                      As Long

 With ActiveSheet
If .FilterMode Then .showalldata
        .AutoFilterMode = False
        .Range("A17").EntireRow.Hidden = True
        End With
        
          
    
   With Application
      .EnableEvents = False
      .ScreenUpdating = False
      lCalcMode = .Calculation
      .Calculation = xlCalculationManual
   End With

   With ActiveSheet
      .EnableFormatConditionsCalculation = False
      .Range("A1:AI300").FormatConditions.Delete

      With .Range("A17:K190,A193:E275")

         With .Font
            .Name = "Arial"
            .Size = 9
            .Superscript = False
            .Subscript = False

         End With

         .HorizontalAlignment = xlLeft
         .VerticalAlignment = xlCenter

      End With

      
      
      Range("Rendate").Select
        Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=(MONTH(L17)&YEAR(L17)<>MONTH($A$1)&(YEAR($A$1)))"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False



      ActiveWindow.Zoom = 80

      For Each rArea In .Range("Account,AccountAPRP").Areas
         rArea.Value = Application.Proper(rArea.Value)
      Next rArea

      For Each rArea In .Range("Subnr,SubnrAPRP,UW").Areas
         vData = rArea.Value
         For x = LBound(vData, 1) To UBound(vData, 1)
            For y = LBound(vData, 2) To UBound(vData, 2)
               vData(x, y) = UCase(vData(x, y))
            Next y
         Next x
         rArea.Value = vData
      Next rArea

      .Range("A18:F190,I18:K190,A193:C275,E193:E275").NumberFormat = "General"

      With .Range("M17:N190, G17:H190, D193:D275")
         .NumberFormat = "[$$-409]#,##0"
         .HorizontalAlignment = xlRight

      End With
      
      Range("D17:D190").Select
    With Selection.validation
        .Delete
        .Add Type:=xlValidateTextLength, AlertStyle:=xlValidAlertStop, _
        Operator:=xlEqual, Formula1:="2"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = "Please enter one UW only"
        .ShowInput = False
        .ShowError = True
    End With
    

      With .Range("Subnr")
         .Cells(1).Select
         .FormatConditions.Add Type:=xlExpression, Formula1:="=AND(F17=""Renewal"",A17="""",P17=""Live"")"
         .FormatConditions(.FormatConditions.Count).SetFirstPriority
         .FormatConditions(1).StopIfTrue = False
         With .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 255
            .TintAndShade = 0
         End With
      End With

      With .Range("RenDate")
         .Cells(1).Select
         .FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=AND(L17="""",A17<>"""",C17<>""Nordeuropa Norway"",C17<>""Nordeuropa Sweden"",C17<>""Nordeuropa"",C17<>""Gravity"",C17<>""Gravity Leaseguard"",C17<>""Arachas"",C17<>""Arachas Leisure"",C17<>""Austagencies"")"
         .FormatConditions(.FormatConditions.Count).SetFirstPriority
         .FormatConditions(1).StopIfTrue = False
         With .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 255
            .TintAndShade = 0
         End With
      End With
      
      With .Range("Status")
         .Cells(1).Select
        
                       
         .FormatConditions.Add Type:=xlExpression, Formula1:="=IF(J17="""","""",IF(AND(J17<>""Red"",I17<>""Bound""),TODAY()>=L17))"
         .FormatConditions(.FormatConditions.Count).SetFirstPriority
         .FormatConditions(1).StopIfTrue = False
         With .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 6750105
            .TintAndShade = 0
         End With
         
         End With
      
      With .Range("Status")
         .Cells(1).Select
        
                       
         .FormatConditions.Add Type:=xlExpression, Formula1:="=IF(J17="""","""",IF(AND(J17<>""Red"",I17<>""Bound""),TODAY()>=L17+3))"
         .FormatConditions(.FormatConditions.Count).SetFirstPriority
         .FormatConditions(1).StopIfTrue = False
         With .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 65535
            .TintAndShade = 0
         End With
         
         End With
         
          With .Range("Status")
         .Cells(1).Select
        
                       
         .FormatConditions.Add Type:=xlExpression, Formula1:="=IF(J17="""","""",if(AND(J17<>""Red"",I17<>""Bound""),TODAY()>=L17+5))"
         .FormatConditions(.FormatConditions.Count).SetFirstPriority
         .FormatConditions(1).StopIfTrue = False
         With .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 255
            .TintAndShade = 0
         End With
         
         End With

      With .Range("Source")
         .FormatConditions.Add Type:=xlTextString, String:="EU Corporate", TextOperator:=xlContains
         .FormatConditions(.FormatConditions.Count).SetFirstPriority
         .FormatConditions(1).StopIfTrue = False
         With .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 10092288
            .TintAndShade = 0
         End With
      End With

      With .Range("Chklist")
         .Cells(1).Select
         .FormatConditions.Add Type:=xlExpression, Formula1:="=AND(O17=""N"",P17=""Live"")"
         .FormatConditions(.FormatConditions.Count).SetFirstPriority
         .FormatConditions(1).StopIfTrue = False
         With .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 255
            .TintAndShade = 0
         End With
      End With

      With .Range("Likelihood")
         .FormatConditions.Add Type:=xlTextString, String:="Amber", TextOperator:=xlContains
         .FormatConditions(.FormatConditions.Count).SetFirstPriority
         .FormatConditions(1).StopIfTrue = False
         With .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 49407
            .TintAndShade = 0
         End With

         .FormatConditions.Add Type:=xlTextString, String:="Green", TextOperator:=xlContains
         .FormatConditions(.FormatConditions.Count).SetFirstPriority
         .FormatConditions(1).StopIfTrue = False
         With .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 13434777
            .TintAndShade = 0
         End With

         .FormatConditions.Add Type:=xlTextString, String:="Red", TextOperator:=xlContains
         .FormatConditions(.FormatConditions.Count).SetFirstPriority
         .FormatConditions(1).StopIfTrue = False
         With .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 8420607
            .TintAndShade = 0
         End With
      End With

      With .Range("Source")
         .FormatConditions.Add Type:=xlTextString, String:="Company", TextOperator:=xlContains
         .FormatConditions(.FormatConditions.Count).SetFirstPriority
         .FormatConditions(1).StopIfTrue = False
         With .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 14734864
            .TintAndShade = 0
         End With

         .FormatConditions.Add Type:=xlTextString, String:="Syndicate", TextOperator:=xlContains
         .FormatConditions(.FormatConditions.Count).SetFirstPriority
         .FormatConditions(1).StopIfTrue = False
         With .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 65535
            .TintAndShade = 0
         End With

         .FormatConditions.Add Type:=xlTextString, String:="EU Corp Lloyds", TextOperator:=xlContains
         .FormatConditions(.FormatConditions.Count).SetFirstPriority
         .FormatConditions(1).StopIfTrue = False
         With .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 16737945
            .TintAndShade = 0
            End With
      End With

      With .Range("Account")
         .Cells(1).Select
         .FormatConditions.Add Type:=xlExpression, Formula1:="=J17=""Red"""
         .FormatConditions(.FormatConditions.Count).SetFirstPriority
         .FormatConditions(1).StopIfTrue = False
         With .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 8420607
            .TintAndShade = 0
         End With

         .FormatConditions.Add Type:=xlExpression, Formula1:="=J17=""Amber"""
         .FormatConditions(.FormatConditions.Count).SetFirstPriority
         .FormatConditions(1).StopIfTrue = False
         With .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 49407
            .TintAndShade = 0
         End With

         .FormatConditions.Add Type:=xlExpression, Formula1:="=J17=""Green"""
         .FormatConditions(.FormatConditions.Count).SetFirstPriority
         .FormatConditions(1).StopIfTrue = False
         With .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 13434777
            .TintAndShade = 0
         End With
      End With

      With .Range("SourceAPRP")
         .FormatConditions.Add Type:=xlTextString, String:="EU Corporate", TextOperator:=xlContains
         .FormatConditions(.FormatConditions.Count).SetFirstPriority
         .FormatConditions(1).StopIfTrue = False
         With .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 10092288
            .TintAndShade = 0
         End With

         .FormatConditions.Add Type:=xlTextString, String:="Syndicate", TextOperator:=xlContains
         .FormatConditions(.FormatConditions.Count).SetFirstPriority
         .FormatConditions(1).StopIfTrue = False
         With .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 65535
            .TintAndShade = 0
         End With

         .FormatConditions.Add Type:=xlTextString, String:="Company", TextOperator:=xlContains
         .FormatConditions(.FormatConditions.Count).SetFirstPriority
         .FormatConditions(1).StopIfTrue = False
         With .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 14734864
            .TintAndShade = 0
         End With
         
         .FormatConditions.Add Type:=xlTextString, String:="EU Corp Lloyds", TextOperator:=xlContains
         .FormatConditions(.FormatConditions.Count).SetFirstPriority
         .FormatConditions(1).StopIfTrue = False
         With .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 16737945
            .TintAndShade = 0
            End With

      End With
      .Range("A1").NumberFormat = "mmm-yy"

      .Columns.AutoFit
      .Range("A15").Select
      .EnableFormatConditionsCalculation = True
   End With

   With Application
      .EnableEvents = True
      .Calculation = lCalcMode
      .ScreenUpdating = True
   End With
   


Call HideRows

End Sub