{"id":2174,"date":"2026-07-09T16:05:44","date_gmt":"2026-07-09T08:05:44","guid":{"rendered":"https:\/\/xlwings.net\/blog\/?p=2174"},"modified":"2026-03-28T09:35:29","modified_gmt":"2026-03-28T09:35:29","slug":"how-to-use-applicationreplaceformat-in-the-xlwings-api-way","status":"publish","type":"post","link":"https:\/\/xlwings.net\/blog\/how-to-use-applicationreplaceformat-in-the-xlwings-api-way\/","title":{"rendered":"How to use Application.ReplaceFormat in the xlwings API way"},"content":{"rendered":"\n

The ReplaceFormat<\/code> member of the Application<\/code> 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<\/code> and Replace<\/code> operation. It acts as a container for a Range<\/code> 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.<\/p>\n\n\n\n

Functionality and Syntax<\/strong><\/p>\n\n\n\n

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

The core syntax within a replacement operation is:<\/p>\n\n\n\n

import xlwings as xw\n\napp = xw.apps.active # Get the active Excel application\napp.api.ReplaceFormat.<Property> = <Desired Value><\/code><\/pre>\n\n\n\n
Here, app.api<\/code> gives direct access to the underlying Excel VBA object model. The .ReplaceFormat<\/code> returns a Range<\/code> object whose properties you set to define the new format. You then pass this format object to the replace<\/code> method.<\/p>\n\n\n\n
The full replace<\/code> method call looks like this:<\/p>\n\n\n\n
range_to_search.replace(what, replacement, replaceformat=app.api.ReplaceFormat)<\/code><\/pre>\n\n\n\n
    \n
  • what<\/code>: The string to find.<\/li>\n\n\n\n
  • replacement<\/code>: The string to replace it with.<\/li>\n\n\n\n
  • replaceformat<\/code>: (Optional) The format to apply to the replacement cells. This is where you pass the app.api.ReplaceFormat<\/code> object after configuring its properties.<\/li>\n<\/ul>\n\n\n\n
    Key Properties of ReplaceFormat Object<\/strong>
    You can set numerous properties. Common ones include:<\/p>\n\n\n\n
    Property (via .api)<\/th>Description<\/th>Example Value<\/th><\/tr><\/thead>
    .Font.Bold<\/code><\/td>Sets the font weight.<\/td>True<\/code> or False<\/code><\/td><\/tr>
    .Font.Color<\/code><\/td>Sets the font color (RGB).<\/td>(255, 0, 0)<\/code> for red<\/td><\/tr>
    .Font.Size<\/code><\/td>Sets the font size.<\/td>12<\/code><\/td><\/tr>
    .Interior.Color<\/code><\/td>Sets the cell background color.<\/td>(0, 255, 0)<\/code> for green<\/td><\/tr>
    .NumberFormat<\/code><\/td>Sets the number format code.<\/td>\"$#,##0.00\"<\/code><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
    Code Examples<\/strong><\/p>\n\n\n\n
      \n
    1. Simple Format Replacement:<\/strong> Find all cells containing “OldValue” and change their background to yellow, regardless of the cell content.<\/li>\n<\/ol>\n\n\n\n
      import xlwings as xw\n\napp = xw.apps.active\nsheet = app.books.active.sheets[0]\n\n# Define the REPLACEMENT format (yellow fill)\napp.api.ReplaceFormat.Interior.Color = (255, 255, 0) # Yellow RGB\n\n# Perform the replace. We search for \"OldValue\", replace with the same text,\n# but apply the yellow fill format.\nsheet.used_range.replace(\"OldValue\", \"OldValue\", replaceformat=app.api.ReplaceFormat)\n\n# Clear the ReplaceFormat to avoid affecting subsequent operations\napp.api.ReplaceFormat.Clear<\/code><\/pre>\n\n\n\n
        \n
      1. Find and Replace with Content & Format Change:<\/strong> 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.<\/li>\n<\/ol>\n\n\n\n
        import xlwings as xw\n\napp = xw.apps.active\nsheet = app.books.active.sheets['Data']\n\n# First, define the FIND format (bold).\napp.api.FindFormat.Font.Bold = True\n\n# Next, define the REPLACEMENT format (blue, italic).\napp.api.ReplaceFormat.Font.Color = (0, 0, 255) # Blue\napp.api.ReplaceFormat.Font.Italic = True\n\n# Perform the replace. The `searchformat` parameter uses the FindFormat.\nsheet.used_range.replace(\nwhat=\"Budget\",\nreplacement=\"Forecast\",\nsearchformat=app.api.FindFormat, # Must match bold cells\nreplaceformat=app.api.ReplaceFormat # Apply blue\/italic\n)\n\n# Clear both format objects after use.\napp.api.FindFormat.Clear\napp.api.ReplaceFormat.Clear<\/code><\/pre>\n\n\n\n
        <\/p>\n","protected":false},"excerpt":{"rendered":"
        The `ReplaceFormat` member of the `Application` object in Excel, when accessed via xlwings, provides…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[25],"tags":[],"class_list":["post-2174","post","type-post","status-publish","format-standard","hentry","category-xlwings-api-reference"],"_links":{"self":[{"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/posts\/2174","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/comments?post=2174"}],"version-history":[{"count":1,"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/posts\/2174\/revisions"}],"predecessor-version":[{"id":3324,"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/posts\/2174\/revisions\/3324"}],"wp:attachment":[{"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/media?parent=2174"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/categories?post=2174"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/xlwings.net\/blog\/wp-json\/wp\/v2\/tags?post=2174"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}