I found how to apply conditonal formatting to a row in excel using pandas Excelwriter here and this works fine, but I want to apply 5 different conditonal formats. I could just copy/paste the conditonal formatting line but this feels like the wrong way to do it particularly as I might need to change it later. I think I should be able to do this with a dictionary and a for loop but I can't make it work. Can someone tell me where I have gone wrong please?
formatC = workbook.add_format({'bg_color': '#a2ed93','font_color': '#000000'})
formatCo = workbook.add_format({'bg_color': '#9d9e9d','font_color': '#000000'})
formatE = workbook.add_format({'bg_color': '#76aac4','font_color': '#000000'})
formatNM = workbook.add_format({'bg_color': '#c488cf','font_color': '#000000'})
formatO = workbook.add_format({'bg_color': '#e87ba1','font_color': '#000000'})
formats={"C":formatC,"Co":formatCo,"E":formatE,"NM":formatNM,"O":formatO}
for stat,form in formats.items():
worksheet.conditional_format('A2:N40', {"type": "formula","criteria": f'=INDIRECT("N"&ROW())={stat}',"format": form})
When I open the output from this in Excel it tells me there is a problem with the content and the recovery process removes the conditional formatting