# Trouble with Conditional Formatting in Excel using Python

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

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!

the output of the code is this

Negative Dynamic value from Column B: -0.0099
Checking cell B6: Value = -0.0166
Stopping further processing for Column B at cell B7
Negative Dynamic value from Column C: -0.0252
Checking cell C6: Value = -0.0355
Checking cell C7: Value = -0.0279
Stopping further processing for Column C at cell C8
Negative Dynamic value from Column D: -0.0185
Stopping further processing for Column D at cell D6
Negative Dynamic value from Column E: -0.0115
Checking cell E6: Value = -0.0553
Stopping further processing for Column E at cell E7
Modified Excel file saved.

If you notice it fails to recognize the value in E7 is less than equal to -0.0115 but it exits the code. It gives the desired output for columns B, C and D.
thanks once again.

It won’t always check all of the rows from 6 to 16, but only those from 6 to the first value that’s greater than the negative dynamic value.

The value in E6 is not less than equal to the negative dynamic value, therefore it leaves the loop because of the `break` in the `else` branch of the `if` statement.

1 Like

It won’t always check all of the rows from 6 to 16, but only those from 6 to the first value that’s greater than the negative dynamic value.

See what i require is the code checks row 6, if it satisfies the condition which in my case is *value is less than and equal to - the dynamic value of that column, if true, proceed to the next row, if false, exit the code.

As you can see in the results it does that it check E6 against -E2, since it satisfies the condition it (in output excel sheet the font is made red). It moves to the E7, here E7 value is -0.009 which is also less than -0.0115 (-E2), so the code should make this red as well no? since condition is true for this as well. instead it Stopping further processing for Column E at cell E7
This is very confusing. i am not so advance in coding i just started a month back.

``````>>> -0.009 <= -0.0115
False
``````

Both are negative, but -0.009 is not as negative as -0.0115.

If you place them on a number line, -0.009 is 0.009 units from zero in the negative direction and -0.0115 is 0.0115 units from zero in the negative direction. Which one is further from zero in the negative direction?

1 Like

OH. MY. GOD…
I just realized that while learning python I completely forgot how to do basic maths.

Thank you so much Matthew. I really feel embarrassed right now. I wish i can hid my head in a mound of sand.

When you compare them using the less than or equal to (`<=`) operator, you are asking -0.009 is less than or equal to -0.0115.
-0.009 is actually greater than -0.0115, the result of this comparison is `False`.

1 Like

Thank you for your kind reply. yes i just realized it i am an idiot.
insert I may be stupid meme here