Hello everyone,
I’m working on a Python script to perform conditional formatting on an Excel spreadsheet. The goal is to format cells based on specific conditions. I’ve been following a logic that compares values in certain cells to dynamic values from other cells.
However, I’m encountering a challenge where the formatting isn’t being applied as expected. Even though I’ve tried different approaches, I can’t seem to get the desired behavior.
Issue:
- I have an Excel file with data in columns B, C, D, and E.
- I’m comparing values in rows 6 to 16 against dynamic values from B2, C2, D2, and E2.
- If the value in a cell is less than or equal to the negative dynamic value, I want to format the cell’s font color as red.
My Code
import openpyxl
Load the Excel file and sheet
workbook = openpyxl.load_workbook(‘testing.xlsx’)
sheet = workbook[‘Set1’]
Get the dynamic values from B2, C2, D2, and E2
dynamic_value_b = sheet[‘B2’].value
dynamic_value_c = sheet[‘C2’].value
dynamic_value_d = sheet[‘D2’].value
dynamic_value_e = sheet[‘E2’].value
Loop through columns B, C, D, and E
for col in range(2, 6): # Columns B to E
dynamic_value = [dynamic_value_b, dynamic_value_c, dynamic_value_d, dynamic_value_e][col - 2] # Choose the corresponding dynamic value
negative_dynamic_value = -dynamic_value # Calculate the negative dynamic value
print(f"Negative Dynamic value from Column {chr(ord(‘A’) + col - 1)}: {negative_dynamic_value}")
# Loop through the cells in the current column from row 6 to 16
for row in range(6, 17):
cell = sheet.cell(row=row, column=col)
# Check if the value is less than or equal to negative_dynamic_value
if cell.value <= negative_dynamic_value:
print(f"Checking cell {chr(ord('A') + col - 1)}{row}: Value = {cell.value}")
# Format the font color as red
cell.font = openpyxl.styles.Font(color='FF0000') # Red color
else:
print(f"Stopping further processing for Column {chr(ord('A') + col - 1)} at cell {chr(ord('A') + col - 1)}{row}")
break # Stop further processing for this column
Save the modified Excel file
workbook.save(‘modified_testing.xlsx’)
print(“Modified Excel file saved.”)
A sample of my Data on which i am applying the code
Dynamic value in B2: 0.0099
Dynamic value in C2: 0.0252
Dynamic value in D2: 0.0185
Dynamic value in E2: 0.0115
Values in column B from row 6 to 16:
B6: -0.0166
B7: 0.0427
B8: 0.0058
B9: -0.034
B10: -0.0048
B11: -0.0141
B12: 0.0091
B13: -0.0032
B14: 0.0008
B15: 0.0246
B16: 0.0119
Values in column C from row 6 to 16:
C6: -0.0355
C7: -0.0279
C8: 0.0411
C9: 0.0251
C10: -0.0267
C11: -0.0216
C12: 0.0551
C13: 0.0111
C14: 0.0214
C15: -0.062
C16: -0.0057
Values in column D from row 6 to 16:
D6: -0.0044
D7: -0.0173
D8: -0.031
D9: -0.0024
D10: 0.0133
D11: -0.0113
D12: 0.0066
D13: -0.0136
D14: -0.031
D15: -0.0011
D16: -0.0624
Values in column E from row 6 to 16:
E6: -0.0553
E7: -0.009
E8: 0.0167
E9: 0.0073
E10: -0.0062
E11: 0.0215
E12: -0.0166
E13: 0.0287
E14: 0.0103
E15: -0.0335
E16: 0.0452
Challenge: I’ve attempted to use the provided logic, but it doesn’t seem to work as intended. Even though some values meet the condition, the formatting is inconsistent. I suspect there might be a flaw in how I’m comparing the values.
Could someone kindly review the code and logic to help me identify where I might be going wrong? Any guidance or suggestions would be greatly appreciated.
Thank you!
