title: Use xlsxwriter to color the content contained in a row, but not lock the color to that row number?
body: <p>I'm using python with the pandas and xlsxwriter modules to format an excel file that is generated dynamically. I need certain rows to be colored yellow, depending on the content of one column. Here is my code ("data" is a pandas DataFrame where each row represents one person). Look for the # comment that points you to the relevant code I'm talking about:  </p>
  
  <pre><code>def format_excel(data):
  
      writer = pd.ExcelWriter('InviteList.xlsx', engine='xlsxwriter')
  
      data.style.to_excel(writer, sheet_name='Sheet1', startrow=1, header=False, index=False)
  
      workbook  = writer.book
      worksheet = writer.sheets['Sheet1']
  
      # Styles
      yellow = workbook.add_format({'bg_color': '#fdf2d0', 'border': 1, 'border_color': '#C0C0C0'})
  
      # Hacky bypass of default header style that pandas imposes  
      for idx, val in enumerate(data.columns):
          worksheet.write(0, idx, val)
  
      # THIS IS THE RELEVANT CODE
      for row, employee in data.iterrows():
          if data.loc[row, 'rsvp'] == 'maybe':
              worksheet.conditional_format(row + 1, 0, row + 1, 15, {'type': 'no_errors', 'format': yellow})
  
      writer.save()
  </code></pre>
  
  <p>So basically the for loop checks to see if the row contains a 'maybe' in the 'rsvp' column and if so, uses the yellow formatting object on that row. This works fine, HOWEVER...</p>
  
  <p>Let's say row 4 and 7 get colored yellow in my excel sheet. Now if I select a column and sort that column alphabetically or something like that, the yellow formatting STAYS in rows 4 and 7 instead of MOVING along with the content it needs to stay with. </p>
  
  <p>So it looks like my implementation permanently locks rows 4 and 7 with yellow formatting, when what I need is for it to be more dynamic: it should stick with the rows containing a "maybe" in the 'rsvp' column, no matter where I move them. </p>
  
  <p><strong>EDIT:</strong></p>
  
  <p>Ok I fixed my issue by using <code>worksheet.set_row</code> instead of <code>worksheet.conditional_format</code>. But then I had an issue where the color would stick with the correct row but other formatting like font size and text wrap wouldn't, so I also had to include those in the definition of my yellow format object. </p>