Python Forum

Hi All,

I am struggling with something and looking everywhere for a clue. The scenario is this: I am searching column 2 of a spreadsheet for a value in a list called subid. See code below.

cells_in_row_with_subid_match = []
for row in ws2.iter_rows(min_col=2, max_col=2):
    for cell in row:
        if cell.value in subID:
            #this next line appends the list of rows in a list
            cells_in_row_with_subid_match.append(cell.row)

That above code produces a list of row with the subid value: [10, 18, 529, 657]

I now want to patternfill each of the rows in the list using:

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

I can do that one row at a time like this:

for rows in ws2.iter_rows(min_row=13, max_row=13, min_col=1):
    for cell in rows:
      cell.fill = PatternFill(start_color='c9e1f8',end_color='c9e1f8',fill_type='solid')

But how do I loop through the list of rows [10, 18, 529, 657] and apply the color?

I have a list:

[10, 18, 529, 657]

I want to loop through the above list and replace XX in the first line below with 10 then 18 then 529 and 657 :

for rows in ws2.iter_rows(min_row=XX, max_row=XX, min_col=1):
    for cell in rows:
      cell.fill = PatternFill(start_color='c9e1f8',end_color='c9e1f8',fill_type='solid')

so it would look below for example:

for rows in ws2.iter_rows(min_row=10, max_row=10, min_col=1):
    for cell in rows:
      cell.fill = PatternFill(start_color='c9e1f8',end_color='c9e1f8',fill_type='solid')

I am trying to grasp how to approach this. Any suggestions?

why do you need to create list of row numbers and then iterate again over rows?

for cell in ws2['B']: # iterate over cells in column B
    if cell.value in subID: # check cell value
        for row_cell in ws2[cell.row]: #iterate over cell in the row
            row_cell.fill = PatternFill(start_color='c9e1f8',end_color='c9e1f8',fill_type='solid')

not tested but it should work

That worked. Thank you so much!