Find and Replace All

' Use the following macro to find all occureneces of "OLD TEXT" and replace it with "NEW TEXT";

Sub MyOffset()

With Range("A1:Z100")

  Set Rng = .Find(What:="OLD TEXT", LookAt:=xlWhole, LookIn:=xlValues)

  If Not Rng Is Nothing Then

    firstAddress = Rng.Address

    Do

      Rng.Value = "NEW TEXT"

      Set Rng = .FindNext(Rng)

    Loop While (Not Rng Is Nothing) Or (Rng.Address <> firstAddress

  End If

End With
End Sub