I may need to change a value of a cell to a specific value that I want, it is the “replace” function in macro so you may wondering “why to use it if I can do it without coding ?”. The macro is quicker but the best answer will be, for instance, you want to change cells with a specific date into “next coming month”, well the “replace” function will not be able to do it.

When I use the macro ?

Usually I used it to change a date value to something like “next month”.

How to create the macro ?

Read How to create, edit, hide and select a macro in an excel report

How to create the button to associate it with the macro ?

Read How to create a button and associated it to a macro in an excel report

How is/are the macro(s) ?

Copy the code below and paste it into your macro. You will see my comments in green if exist so follow the help to adapt to your need.

Based on cell range, for instance, to change a date value to a specific word:

Sub test()
Dim MyRange As Range
Dim MyCell As Range
' changethe range
Set MyRange = Range("B2:B20")
For Each MyCell In MyRange.Cells
  If VarType(MyCell) = vbDate Then
    ' changedate if you are searching something else
    If MyCell >= Int(DateSerial(Year(Date), Month(Date), 1)) Then
      ' change month by the new word you want to replace
      MyCell = "month"
    End If
  End If
Next MyCell
End Sub

Based on column range, for instance, to change a word by another word:

Sub test()
Dim Rang As Range
Dim Cell As Range
With ActiveSheet
  ' changeA:B  by your column range
  Set Rang = Intersect(.Columns("A:B"), .UsedRange)
End With
For Each Cell In Rang
  ' change month by the word you are searching
  If Cell.Value >= "month" Then
    ' change out of date by the new word you want to replace
    Cell.Value = "out of date"
  End If
Next Cell
End Sub