VBA remove the leading apostrophes and format

[Bonz]

Morning All,

I would like to remove the leading apostrophes from numbers stored as text in column A and preserve the leading zeros by formatting the value. The below procedure works if I run it twice but after the first run all it seems to do is to cause Excel to show the green tick mark for the cells error message and I’m not sure why?

Thanks in advance for any help!

Sub DeathToApostrophe()
    Dim s As Range
 
    Application.ScreenUpdating = False
    
    For Each s In ActiveSheet.Range("A2:A100")
        
        If s.HasFormula = False Then
             'Gets text and rewrites to same cell without   the apostrophe.
              s.Value = Format(s.Value, "000000")
            
        End If
        
    Next s
     
    Application.ScreenUpdating = True
End Sub