Python Forum
Trying to color an excel row based on list
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Trying to color an excel row based on list
#1
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
Reply
#2
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
Reply
#3
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
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Order a list with successive permutations based on another list yvrob 3 235 Mar-19-2021, 08:20 AM
Last Post: supuflounder
Question How to make a 3D List of Excel Spreadsheets? chatguy 4 306 Jan-24-2021, 05:24 AM
Last Post: buran
  Convert Excel to complex list and2handles 1 501 Jun-23-2020, 01:51 PM
Last Post: DPaul
  Make nested system directories based on an unsorted list? koebi 0 480 Mar-25-2020, 01:14 PM
Last Post: koebi
  How to list out specific excel files ajay_pal7 2 797 Mar-10-2020, 05:43 AM
Last Post: Larz60+
  Creating new list based on exact regex match in original list interjectdirector 1 681 Mar-08-2020, 09:30 PM
Last Post: deanhystad
  Filtering Excel Document Data Based On Numerical Values eddywinch82 30 4,280 Feb-25-2020, 06:08 PM
Last Post: eddywinch82
  Need to identify sheet color in excel workbook chewy1418 2 731 Feb-14-2020, 03:26 PM
Last Post: chewy1418
  Excel sheet by color abt_python 1 928 Jan-18-2020, 10:27 PM
Last Post: Clunk_Head
  Exporting list with dictionary to Excel veromi22 0 922 Oct-15-2019, 12:54 AM
Last Post: veromi22

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020