Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
ReplaceFormat Property
Collapse the table of content
Expand the table of content

Application.ReplaceFormat Property (Excel)

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.

expression .ReplaceFormat

expression A variable that represents an Application object.

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.

Sub MakeBold() 
 ' Establish search criteria. 
 With Application.FindFormat.Font 
 .Name = "Arial" 
 .FontStyle = "Regular" 
 .Size = 10 
 End With 
 ' Establish replacement criteria. 
 With Application.ReplaceFormat.Font 
 .Name = "Arial" 
 .FontStyle = "Bold" 
 .Size = 8 
 End With 
 ' Notify user. 
 With Application.ReplaceFormat.Font 
 MsgBox .Name & "-" & .FontStyle & "-" & .Size & _ 
 " 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
© 2015 Microsoft