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
  Copy Paste excel files based on the first letters of the file name Viento 2 346 Feb-07-2024, 12:24 PM
Last Post: Viento
  unable to remove all elements from list based on a condition sg_python 3 371 Jan-27-2024, 04:03 PM
Last Post: deanhystad
  Search Excel File with a list of values huzzug 4 1,147 Nov-03-2023, 05:35 PM
Last Post: huzzug
  Turtle Star Fill Color Yellow-White Interchanging Color Effect codelab 9 900 Oct-25-2023, 09:09 AM
Last Post: codelab
  trouble reading string/module from excel as a list popular_dog 0 383 Oct-04-2023, 01:07 PM
Last Post: popular_dog
  Color a table cell based on specific text Creepy 11 1,821 Jul-27-2023, 02:48 PM
Last Post: deanhystad
  Deleting rows based on cell value in Excel azizrasul 11 2,472 Oct-19-2022, 02:38 AM
Last Post: azizrasul
  list from excel devilonline 4 1,331 Jun-22-2022, 11:00 PM
Last Post: devilonline
  select Eof extension files based on text list of filenames with if condition RolanRoll 1 1,474 Apr-04-2022, 09:29 PM
Last Post: Larz60+
  Compare two Excel sheets with Python and list diffenrences dmkfon 1 14,481 Oct-09-2021, 03:30 PM
Last Post: Larz60+

Forum Jump:

User Panel Messages

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