Apply conditional cell formatting to an excel sheet using python openpyxl

As part of a little hobby project I'm attempting to add some formatting to cells in an excel worksheet before uploading it to google drive.

The cells can have either 0, 1 or 2 and depending on the value I apply a color (white, black or grey respectively).

Here is my attempt:

from openpyxl import load_workbook
from openpyxl.styles import PatternFill
from openpyxl.styles.differential import DifferentialStyle
from openpyxl.formatting.rule import Rule
from openpyxl.cell.cell import Cell

# Load the Excel workbook
workbook = load_workbook('output.xlsx')

# Select the desired sheet by name or index
sheet = workbook['Sheet1']  # Replace 'Sheet1' with the actual sheet name

# Create the conditional formatting rules
rules = [
    {
        'value': 1,
        'fill_color': '00000000'  # Black
    },
    {
        'value': 2,
        'fill_color': 'FF808080'  # Solid Grey
    }
]

for rule in rules:
    fill = PatternFill(start_color=rule['fill_color'], end_color=rule['fill_color'], fill_type='solid')

    # Apply conditional formatting rule
    for row in sheet.iter_rows(min_row=2, values_only=True):  # Start from the second row assuming headers are in the first row
        for cell in row:
            if isinstance(cell, Cell) and cell.value == rule['value']:
                cell.fill = fill

# Save the modified workbook with conditional formatting
workbook.save('output_formatted.xlsx')

The code seems to run but output_formatted.xlsx doesn't seem to have any formatting. Do you know how I'm going wrong or could you suggest a better way to solve this?

I expect the output to look like this:

image of excel sheet cells color coded scheme

if it helps here is an excerpt of the first few rows of data (apologies in advance for the poor formatting):

| normalM | normalF | shinyM | shinyF | darkM | darkF | goldenM | goldenF |
|---------|---------|--------|--------|-------|-------|---------|---------|
|       0 |       0 |      1 |      0 |     1 |     0 |       1 |       0 |
|       0 |       0 |      0 |      0 |     0 |     0 |       1 |       0 |
|       1 |       0 |      0 |      1 |     1 |     0 |       0 |       0 |
|       0 |       0 |      1 |      1 |     1 |     1 |       1 |       1 |
|       1 |       0 |      1 |      1 |     1 |     0 |       1 |       0 |
|       1 |       2 |      0 |      2 |     1 |     2 |       1 |       2 |
|       1 |       2 |      0 |      2 |     1 |     2 |       1 |       2 |
|       1 |       1 |      1 |      1 |     0 |     1 |       1 |       1 |
|       0 |       0 |      0 |      0 |     0 |     0 |       0 |       0 |
|       0 |       0 |      1 |      0 |     0 |     0 |       1 |       1 |
|       0 |       1 |      0 |      0 |     1 |     0 |       0 |       1 |

Any words of wisdom much appreciated. thank you. PS: I know pandas as well if you have any suggestions to solve it using pandas I would be happy to try them out.

You mention Conditional Formatting Rules in the code so not really sure if you are trying to achieve this using CF or just loop through and change the fill based on the value in each cell.
In a case like this, generally CF is better. It's only necessary to set the range to cover as many or few cells as required rather than loop every cell and if you want to change the cell values later they will update dynamically.

If CF is your preference you'd want something like this;
Assumes the range C1 to J12 (with row 1 being the Header row -
EDIT
I didn't bother with it since there were only two rules, but if you wanted to include more fills then you can change the code to add each CF rule in a loop as shown in the updated code;

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

# Create fill
blackFill = PatternFill(start_color='000000', end_color='000001', fill_type='solid')
greyFill = PatternFill(start_color='808080', end_color='808080', fill_type='solid')

# Load the Excel workbook
workbook = load_workbook('output.xlsx')

# Select the desired sheet by name or index
sheet = workbook['Sheet1']  # Replace 'Sheet1' with the actual sheet name

# sheet.conditional_formatting.add('C2:J12',
                                 # CellIsRule(operator='equal', formula=['1'], stopIfTrue=True, fill=blackFill))

# sheet.conditional_formatting.add('C2:J12',
                                 # CellIsRule(operator='equal', formula=['2'], stopIfTrue=True, fill=greyFill))

colour_dict = {1: blackFill, 2: greyFill}
for x in range(1, 3):
    sheet.conditional_formatting.add('C2:J12',
                                     CellIsRule(operator='equal',
                                                formula=[x],
                                                stopIfTrue=True,
                                                fill=colour_dict[x]
                                                )
                                     )

workbook.save('output_formatted.xlsx')

From the provided data, you'd get a fill like the following.
If cell C2 was changed to 1 the fill would become black and if changed to 2 the fill would become grey.