Python Forum
Trying to color an excel row based on list - Printable Version

+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: General Coding Help (https://python-forum.io/forum-8.html)
+--- Thread: Trying to color an excel row based on list (/thread-21570.html)



Trying to color an excel row based on list - curranjohn46 - Oct-04-2019

Hi,

The code below uses openpyxl and iterates through all rows and columns in a spreadsheet searching for a value in a list called subID. It then colors each cell it finds with that value light blue. I am trying to figure out how to color the whole row when a cell is found.

Any suggestions on how I can do this? I am not 100% sure but openpyxl may be forcing me to color each cell individually in the row.

#this colors the cell wherever sub id in list is found.

lightblueFill = PatternFill(start_color='c9e1f8',
                   end_color='c9e1f8',
                   fill_type='solid')

max_row=ws2.max_row
max_column=ws2.max_column

# iterate over all rows
for ro in range(1,max_row+1):
    
    # iterate over all columns
    for co in range(1,max_column+1):
        
        # get particular cell value 
        cell_obj=ws2.cell(row=ro,column=co) 
        
        #searches all cell and columns for a value in a list called subID
        if cell_obj.value in subID:
        
            #colors the cells found light blue. 
            cell_obj.fill = lightblueFill



RE: Trying to color an excel row based on list - Aurthor_King_of_the_Brittons - Oct-08-2019

I believe this link could help with that type of formatting. Skip to the first example under conditional formatting (the link is in the table of contacts to skip right to it). It shows how to color whole rows based off of an individual cell's value.

Conditional formatting with Python


RE: Trying to color an excel row based on list - KavyaL - May-19-2020

Hi john,
I think this would help you in coloring entire row instead of individual cell

for y in range(1,max_column+1):
ws2.cell(row=ro,column=y).fill = lightblueFill