How to conditionally format columns pertaining to a cell value

Question

I'm trying to color cells within Column J a yellow color where it's cell value is N.

I'm able to achieve this but I also want to color the corresponding cells in columns H & I when Column J is N and am having difficulty doing so.

Does anyone have suggestions on how I can implement this?

This is my code so far:

     
import pandas as pd

filePath = r"C:\\Users\\Desktop\\Match\\"
parameters = 'Transactions.xlsx'

df = pd.read_excel(filePath + parameters,usecols="A:EN")

with pd.ExcelWriter(r"C:\\Users\\Desktop\\Match\\Compare_Matches.xlsx", 
engine='xlsxwriter') as writer:
    workbook = writer.book
    df.to_excel(writer)
    worksheet = workbook.worksheets()[0]

format_yellow = workbook.add_format({'bg_color': '#FFFF00'})

worksheet.conditional_format('J1:J13630', 
                                {'type': 'text',
                                'criteria': 'containing',
                                'value':   'N',
                                'format':   format_yellow,})

score 0 · Answer 1 · answered Mar 18 '22 at 17:33

In this case you probably need a formula type conditional format. Here is a simple example based on yours:

import pandas as pd


# Create a Pandas dataframe from some data.
df = pd.DataFrame({'Data1': ['Y', 'N', 'N', 'Y', 'Y', 'N', 'Y'],
                   'Data2': [100, 200, 300, 200, 150, 300, 450],
                   'Data3': [100, 200, 300, 200, 150, 300, 450]})

# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter('Compare_Matches.xlsx', engine='xlsxwriter')

# Convert the dataframe to an XlsxWriter Excel object.
df.to_excel(writer, sheet_name='Sheet1')

# Get the xlsxwriter workbook and worksheet objects.
workbook = writer.book
worksheet = writer.sheets['Sheet1']

# Get the dimensions of the dataframe.
(max_row, max_col) = df.shape

# Create a format for the conditional format.
format_yellow = workbook.add_format({'bg_color': '#FFFF00'})

# Apply a conditional format to the required cell range.
worksheet.conditional_format(1, 1, max_row, max_col,
                             {'type': 'formula',
                              'criteria': '=$B2="N"',
                              'format': format_yellow})

# Close the Pandas Excel writer and output the Excel file.
writer.save()

Output:

Note, the important part of the formula is $B2 where the $B makes it an absolute reference to column B only and 2 is a relative reference which change with the row.

In general it is best to figure out the conditional format that you want in Excel first and then transfer it to XlsxWriter.

How to conditionally format columns pertaining to a cell value

1 Answers1