Application.ReplaceFormat Property

  • Article
Excel Developer Reference

Sets the replacement criteria to use in replacing cell formats. The replacement criteria is then used in a subsequent call to the Replace method of the Range object.

Syntax

expression.ReplaceFormat

expression   A variable that represents an Application object.

Example

The following example sets the search criteria to find cells containing Arial, Regular, Size 10 font, replaces their formats with Arial, Bold, Size 8 font, and then calls the Replace method, with the optional arguments of SearchFormat and ReplaceFormat set to True to actually make the changes.

Visual Basic for Applications 
  Sub MakeBold()

' Establish search criteria.
With Application.FindFormat.Font
    .Name = "Arial"
    .FontStyle = "Regular"
    .Size = 10
End With

' Establish replacement criteria.
With Application.<strong class="bterm">ReplaceFormat</strong>.Font
    .Name = "Arial"
    .FontStyle = "Bold"
    .Size = 8
End With

' Notify user.
With Application.<strong class="bterm">ReplaceFormat</strong>.Font
    MsgBox .Name &amp; "-" &amp; .FontStyle &amp; "-" &amp; .Size &amp; _
        " font is what the search criteria will replace cell formats with."
End With

' Make the replacements in the worksheet.
Cells.Replace What:="", Replacement:="", _
    SearchFormat:=True, ReplaceFormat:=True



End Sub

See Also