How to use Application.ReplaceFormat in the xlwings API way

The ReplaceFormat member of the Application object in Excel, when accessed via xlwings, provides a powerful way to define the formatting characteristics for cells that will be replaced during a Find and Replace operation. It acts as a container for a Range object’s formatting properties, allowing you to specify complex formatting criteria (like font, interior color, or number format) that must be matched for replacement, or to define the new format to be applied. This is particularly useful for batch formatting changes where you need to find cells based not just on their content but also on their appearance, and then update either the content, the format, or both.

Functionality and Syntax

In xlwings, you typically use this member in conjunction with the Range.replace method (which is the API equivalent of the Excel Find and Replace dialog). The ReplaceFormat property is used to set the replacement format. To specify the find format, you would use the FindFormat property of the Application object.

The core syntax within a replacement operation is:

import xlwings as xw

app = xw.apps.active # Get the active Excel application
app.api.ReplaceFormat.<Property> = <Desired Value>

Here, app.api gives direct access to the underlying Excel VBA object model. The .ReplaceFormat returns a Range object whose properties you set to define the new format. You then pass this format object to the replace method.

The full replace method call looks like this:

range_to_search.replace(what, replacement, replaceformat=app.api.ReplaceFormat)

• what: The string to find.
• replacement: The string to replace it with.
• replaceformat: (Optional) The format to apply to the replacement cells. This is where you pass the app.api.ReplaceFormat object after configuring its properties.

Key Properties of ReplaceFormat Object
You can set numerous properties. Common ones include:

Property (via .api)	Description	Example Value
.Font.Bold	Sets the font weight.	True or False
.Font.Color	Sets the font color (RGB).	(255, 0, 0) for red
.Font.Size	Sets the font size.	12
.Interior.Color	Sets the cell background color.	(0, 255, 0) for green
.NumberFormat	Sets the number format code.	"$#,##0.00"

Code Examples

1. Simple Format Replacement: Find all cells containing “OldValue” and change their background to yellow, regardless of the cell content.

import xlwings as xw

app = xw.apps.active
sheet = app.books.active.sheets[0]

# Define the REPLACEMENT format (yellow fill)
app.api.ReplaceFormat.Interior.Color = (255, 255, 0) # Yellow RGB

# Perform the replace. We search for "OldValue", replace with the same text,
# but apply the yellow fill format.
sheet.used_range.replace("OldValue", "OldValue", replaceformat=app.api.ReplaceFormat)

# Clear the ReplaceFormat to avoid affecting subsequent operations
app.api.ReplaceFormat.Clear

2. Find and Replace with Content & Format Change: Find cells with the word “Budget” that are currently bold, and replace the text with “Forecast” while also changing the font to blue and italic.

import xlwings as xw

app = xw.apps.active
sheet = app.books.active.sheets['Data']

# First, define the FIND format (bold).
app.api.FindFormat.Font.Bold = True

# Next, define the REPLACEMENT format (blue, italic).
app.api.ReplaceFormat.Font.Color = (0, 0, 255) # Blue
app.api.ReplaceFormat.Font.Italic = True

# Perform the replace. The `searchformat` parameter uses the FindFormat.
sheet.used_range.replace(
what="Budget",
replacement="Forecast",
searchformat=app.api.FindFormat, # Must match bold cells
replaceformat=app.api.ReplaceFormat # Apply blue/italic
)

# Clear both format objects after use.
app.api.FindFormat.Clear
app.api.ReplaceFormat.Clear