[Python] Python 3.0: Apply Conditional Formatting to a Cell based off the calculated value of...

I am trying to automate processing of a form with;
(1) two calculated fields in columns F and G, and
(2) one manually-entered field in column H.

1. 
   If both these values in a row are calculated to be >=30, I would like to highlight the corresponding cell in column A.
   
   
2. 
   Alternatively, if the value in column H is "Warranty PM", I want to highlight the corresponding cell in column A in yellow.
   

At this time, I have not started on function (2) as I want it to run after function (1), such that it is the priority function. I apologize if I am messing the naming convention up with Excel!

In case it may help, the following is an example of my data:

Case No Status Current Date Date Created Date Modified Days Since Creation Days Since Modification Type
3051 New [Today] 01-Nov-2024 01-Nov-2024 =DATEDIF(D2,C2,"d") =DATEDIF(E2,C2,"d") Warranty PM
3048 Service Scheduled [Today] 31-Oct-2024 01-Nov-2024 =DATEDIF(D3,C3,"d") =DATEDIF(E3,C3,"d") Hardware
...
2832 Service Scheduled [Today] 20-Aug-2024 27-Aug-2024 =DATEDIF(D16,C16,"d") =DATEDIF(E16,C16,"d") Customer Request (Move)

In my current code, I am able to get both the F and G columns to properly highlight based on their calculated values.
These should be light red if the cell is over 30.
This is completed using conditional formatting:

from openpyxl import load_workbook
from openpyxl.styles import PatternFill, NamedStyle
from openpyxl.formatting.rule import CellIsRule

def format_xlsx(xlsx_file):
#Load the file and workbook
file_path = xlsx_file
workbook = load_workbook(file_path)
sheet = workbook.active

#Define the fills
light_red_fill = PatternFill(start_color="FFC7CE", end_color="FFC7CE", fill_type="solid")
dark_red_fill = PatternFill(start_color="8B0000", end_color="8B0000", fill_type="solid")
yellow_fill = PatternFill(start_color="FFFF00", end_color="FFFF00", fill_type="solid")

#Create conditional format rules for F and G:
sheet.conditional_formatting.add(
"F2:F1048576", # Apply to column F from row 2 onwards
CellIsRule(operator="greaterThanOrEqual", formula=["30"], fill=light_red_fill))

sheet.conditional_formatting.add(
"G2:G1048576", # Apply to column G from row 2 onwards
CellIsRule(operator="greaterThanOrEqual", formula=["30"], fill=light_red_fill))

workbook.save(file_path)
return xlsx_file


However, if I try to include a function that will highlight cells in column A:

from openpyxl import load_workbook
from openpyxl.styles import PatternFill, NamedStyle
from openpyxl.formatting.rule import CellIsRule

def format_xlsx(xlsx_file): #Load the file and workbook file_path = xlsx_file workbook = load_workbook(file_path) sheet = workbook.active
#Define the fills
light_red_fill = PatternFill(start_color="FFC7CE", end_color="FFC7CE", fill_type="solid")
dark_red_fill = PatternFill(start_color="8B0000", end_color="8B0000", fill_type="solid")
yellow_fill = PatternFill(start_color="FFFF00", end_color="FFFF00", fill_type="solid")

#Create conditional format rules for F and G:
sheet.conditional_formatting.add(
"F2:F1048576", # Apply to column F from row 2 onwards
CellIsRule(operator="greaterThanOrEqual", formula=["30"], fill=light_red_fill))

sheet.conditional_formatting.add(
"G2:G1048576", # Apply to column G from row 2 onwards
CellIsRule(operator="greaterThanOrEqual", formula=["30"], fill=light_red_fill))

#Loop through each cell in columns F and G, ignoring the first row for headers.
red = "AND($F2>=30, $G2>=30)"
sheet.conditional_formatting.add(
"A2:A1048576", # Apply to column F from row 2 onwards
CellIsRule(formula=[red], fill=dark_red_fill))

workbook.save(file_path)
return xlsx_file


I get the following error when opening the workbook in excel:

"We found a problem with some content in 'testfile_04-Nov-2024.xlsx'.
Do you want us to try to recover as much as we can? If you trust the source of this workbook, click Yes."


I am then presented with the option to view

Excel was able to open the file by repairing or removing the unreadable content.


and this error message in a word document:

Repair Result to testfile_04-Nov-2024.xlsx Errors were detected in file '/Users/llindgren/Downloads/testfile_04-Nov-2024.xlsx'
Removed Records: Conditional formatting from /xl/worksheets/sheet1.xml part


I also tried using a different function for all the above, but this resulted in no highlighted values:

import openpyxl
from openpyxl.styles import PatternFill

# Load the Excel file
file_path = "your_file.xlsx"
workbook = openpyxl.load_workbook(file_path)
sheet = workbook.active

# Define the fill styles for highlighting
light_red_fill = PatternFill(start_color="FFC7CE", end_color="FFC7CE", fill_type="solid") # Light red for columns F and G
dark_red_fill = PatternFill(start_color="8B0000", end_color="8B0000", fill_type="solid") # Dark red for column A

# Loop through each row and check values in columns F and G
for row in range(2, sheet.max_row + 1): # Starting from row 2 to skip headers if they exist
cell_f = sheet[f"F{row}"]
cell_g = sheet[f"G{row}"]

# Check and highlight cells in F and G if their value >= 30
if cell_f.value is not None and isinstance(cell_f.value, (int, float)):
if cell_f.value >= 30:
cell_f.fill = light_red_fill # Highlight F
print(f"Highlighted F{row} with light red.") # Debug statement

if cell_g.value is not None and isinstance(cell_g.value, (int, float)):
if cell_g.value >= 30:
cell_g.fill = light_red_fill # Highlight G
print(f"Highlighted G{row} with light red.") # Debug statement

# Check if both F and G are >= 30, then apply dark red fill to column A
if (
cell_f.value is not None and cell_g.value is not None and
isinstance(cell_f.value, (int, float)) and isinstance(cell_g.value, (int, float)) and
cell_f.value >= 30 and cell_g.value >= 30
):
sheet[f"A{row}"].fill = dark_red_fill
print(f"Highlighted A{row} with dark red.") # Debug statement

# Save changes to the same file
workbook.save(file_path)
print("File saved successfully.")

Continue reading...