How to use Application.FindFormat in the xlwings API way

The FindFormat property of the Application object in xlwings is a powerful feature for controlling the search criteria in Excel when using methods like Find or Replace. It allows you to define a set of formatting attributes (such as font color, cell fill, or number format) that Excel will use to locate cells matching that specific format. This is particularly useful for automating tasks where you need to find or modify cells based on their visual styling rather than their content.

In xlwings, you access this property through the Application object. The FindFormat property itself returns a FindFormat object. You do not set it directly to a value; instead, you configure its properties (like Font or Interior) to define the search format. After setting these properties, any subsequent Find or Replace operation will use this format as a criterion if the SearchFormat argument is set to True.

Syntax in xlwings:

import xlwings as xw

app = xw.apps.active # Get the active Excel application
find_format = app.api.FindFormat # Access the FindFormat object

Once you have the find_format object, you can set its various properties. Common properties include:

• find_format.Font.Color: Sets the font color (e.g., RGB(255, 0, 0) for red).
• find_format.Interior.Color: Sets the cell background color.
• find_format.Font.Bold: Sets the font to bold (True or False).

After configuring the format, you use it in a Find method. For example, to find the next cell with the specified format:

range_to_search = xw.books.active.sheets[0].api.UsedRange
found_cell = range_to_search.Find(What="", SearchFormat=True)

Note: The What parameter is set to an empty string "" because we are searching by format only. The SearchFormat=True tells Excel to use the format defined in FindFormat.

Example:
Suppose you want to find all cells in a worksheet that have a yellow background. Here’s how you can do it with xlwings:

import xlwings as xw

# Connect to Excel
app = xw.apps.active

# Define the search format: yellow interior
find_format = app.api.FindFormat
find_format.Interior.Color = 65535 # Yellow color in Excel's color index

# Search in the used range of the first sheet
sheet = xw.books.active.sheets[0]
search_range = sheet.api.UsedRange
first_cell = search_range.Find(What="", SearchFormat=True)

# Loop to find all matching cells
if first_cell:
    addresses = [first_cell.Address]
    next_cell = search_range.FindNext(first_cell)
    while next_cell.Address not in addresses:
        addresses.append(next_cell.Address)
        next_cell = search_range.FindNext(next_cell)
        print(f"Cells with yellow background: {addresses}")
else:
    print("No cells found with the specified format.")