# Trouble with Conditional Formatting in Excel using Python

**URL:** <https://discuss.python.org/t/trouble-with-conditional-formatting-in-excel-using-python/32711>\
**Category:** Python Help\
**Tags:** help\
**Created:** [August 31, 2023, 9:34pm UTC](https://discuss.python.org/t/trouble-with-conditional-formatting-in-excel-using-python/32711 "2023-08-31T21:34:20Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![Blackzero68](https://avatars.discourse-cdn.com/v4/letter/b/96bed5/32.png) [@Blackzero68](https://discuss.python.org/u/Blackzero68)\
**Post date:** [August 31, 2023, 9:34pm UTC](https://discuss.python.org/t/trouble-with-conditional-formatting-in-excel-using-python/32711/1 "2023-08-31T21:34:20Z")

</div>

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!

---

<div class="post-metadata">

**Author:** ![Blackzero68](https://avatars.discourse-cdn.com/v4/letter/b/96bed5/32.png) [@Blackzero68](https://discuss.python.org/u/Blackzero68)\
**Post date:** [August 31, 2023, 9:41pm UTC](https://discuss.python.org/t/trouble-with-conditional-formatting-in-excel-using-python/32711/2 "2023-08-31T21:41:22Z")

</div>

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.  
Any help in this regard would be helpful please.  
thanks once again.

---

<div class="post-metadata">

**Author:** ![MRAB](https://avatars.discourse-cdn.com/v4/letter/m/e9c0ed/32.png) [@MRAB](https://discuss.python.org/u/MRAB)\
**Post date:** [August 31, 2023, 11:27pm UTC](https://discuss.python.org/t/trouble-with-conditional-formatting-in-excel-using-python/32711/3 "2023-08-31T23:27:13Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Blackzero68](https://avatars.discourse-cdn.com/v4/letter/b/96bed5/32.png) [@Blackzero68](https://discuss.python.org/u/Blackzero68)\
**Post date:** [September 1, 2023, 6:26am UTC](https://discuss.python.org/t/trouble-with-conditional-formatting-in-excel-using-python/32711/4 "2023-09-01T06:26:48Z")

</div>

Thank you Matthew for your kind reply.

_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.

---

<div class="post-metadata">

**Author:** ![MRAB](https://avatars.discourse-cdn.com/v4/letter/m/e9c0ed/32.png) [@MRAB](https://discuss.python.org/u/MRAB)\
**Post date:** [September 1, 2023, 3:39pm UTC](https://discuss.python.org/t/trouble-with-conditional-formatting-in-excel-using-python/32711/5 "2023-09-01T15:39:57Z")

</div>

```auto
>>> -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?

---

<div class="post-metadata">

**Author:** ![Blackzero68](https://avatars.discourse-cdn.com/v4/letter/b/96bed5/32.png) [@Blackzero68](https://discuss.python.org/u/Blackzero68)\
**Post date:** [September 1, 2023, 4:08pm UTC](https://discuss.python.org/t/trouble-with-conditional-formatting-in-excel-using-python/32711/6 "2023-09-01T16:08:07Z")

</div>

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.

 ![gaaad WHY](https://us1.discourse-cdn.com/flex002/uploads/python1/original/2X/a/a13f10bb212ab61fcc186dfb8ac9fc57e77ca254.jpeg)

---

<div class="post-metadata">

**Author:** ![tushali](https://sea2.discourse-cdn.com/flex002/user_avatar/discuss.python.org/tushali/32/14432_2.png) [@tushali](https://discuss.python.org/u/tushali)\
**Post date:** [September 1, 2023, 5:15pm UTC](https://discuss.python.org/t/trouble-with-conditional-formatting-in-excel-using-python/32711/7 "2023-09-01T17:15:55Z")

</div>

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`.

---

<div class="post-metadata">

**Author:** ![Blackzero68](https://avatars.discourse-cdn.com/v4/letter/b/96bed5/32.png) [@Blackzero68](https://discuss.python.org/u/Blackzero68)\
**Post date:** [September 1, 2023, 5:57pm UTC](https://discuss.python.org/t/trouble-with-conditional-formatting-in-excel-using-python/32711/8 "2023-09-01T17:57:38Z")

</div>

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