title: How to Find and Replace date format in Excel Comments using VBA
body: <p>I have Cells with Comments such as the following:</p>
  
  <blockquote>
    <p>May 26, 2017: Reduced from 1000 to 900</p>
  </blockquote>
  
  <p>I want to use VBA to Find and Replace only the date format within the comments from:</p>
  
  <blockquote>
    <p>MMM DD, YYYY</p>
  </blockquote>
  
  <p>to </p>
  
  <blockquote>
    <p>YYYY/MM/DD</p>
  </blockquote>
  
  <p>The best code I could find after searching for several hours is the following but unfortunately, it does only Find and Replace text with a text. I tried to make it work with formats but I couldn't.</p>
  
  <pre><code>Sub ReplaceComments()
  Dim cmt As Comment
  Dim wks As Worksheet
  Dim sFind As String
  Dim sReplace As String
  Dim sCmt As String
  sFind = "2011"
  sReplace = "2012"
  For Each wks In ActiveWorkbook.Worksheets
  For Each cmt In wks.Comments
  sCmt = cmt.Text
  If InStr(sCmt, sFind) &lt;&gt; 0 Then
  sCmt = Application.WorksheetFunction. _
  Substitute(sCmt, sFind, sReplace)
  cmt.Text Text:=sCmt
  End If
  Next
  Next
  Set wks = Nothing
  Set cmt = Nothing
  End Sub
  </code></pre>