Why is my xlsxwriter conditional formatting not working?

Question

I have two columns in a dataframe named FirstName and LastName.

I need to make the font color of any cell's text in the FirstName column red if any cell in the LastName is not blank.

writer = pd.ExcelWriter(fileName, engine='xlsxwriter')
df.to_excel(writer,'Sheet1', index_label=None, index=False)
workbook  = writer.book
redFont   = workbook.add_format({'font_color': '#9C0006'})
worksheet = writer.sheets['Sheet1']
worksheet.conditional_format(1, 0, 9999999, 0,
                             {'type':     'formula',
                              'criteria': '=ISBLANK($B1)',
                              'format':   redFont})
writer.save()

I do not get errors but the font color does not change. I can not figure out what I am doing wrong. Any ideas?

Thank you!

Answer 1

There are a couple of small errors in the code: the range is bigger than the maximum range allowed by Excel (so XlsxWriter rejects the conditional format) and also if the range starts in cell A2 you should apply the conditional format using the reference cell $B2. Also I think the logic should be reversed to match cells that are not blank

It should be something like this:

import pandas as pd

fileName = 'test.xlsx'

df = pd.DataFrame({'FirstName': ['Anna', 'Bob', 'Cian', 'Dora'],
                   'LastName':  ['Aran', '',    '',     'Dodd']})

writer = pd.ExcelWriter(fileName, engine='xlsxwriter')
df.to_excel(writer, 'Sheet1', index_label=None, index=False)

workbook = writer.book
redFont = workbook.add_format({'font_color': '#9C0006'})
worksheet = writer.sheets['Sheet1']

worksheet.conditional_format(1, 0, 1048575, 0,
                             {'type':     'formula',
                              'criteria': '=ISBLANK($B2)=FALSE',
                              'format':   redFont})
writer.save()

Output: