Trying to search excel for a value in row then format row

[curranjohn46]

Hi,

I am using openpyxl to find and print values in a column of a spreadsheet. and then want to search for those values in a second spreadsheet, and format the row (color it orange to be exact). I need help on understanding how to search for the the printed values in the second spreadsheet.

Here is how I get the values:

from openpyxl import load_workbook
IntLiveFile = "C:\\Users\\Jcurran\\weeklylive.xlsx"
wb=load_workbook(IntLiveFile)
wb.sheetnames
ws = wb.active
#Extracts the subscriber number for accounts that went live last week.
for row in ws.iter_rows("L"):
    for cell in row:
        if cell.value == 'Integration Live':
            print(ws.cell(row=cell.row, column=3).value) 

#above code gives me desired output from spreadsheet:
141680
469897
48670
281174

I then want to search another spreadsheet for those values. Can I somehow output those above numbers (instead of print?) in a manner where I can then iterate through rows in the second spreadsheet and find each one? Then I will figure out the color formatting which I know how to do. Any thoughts or hints appreciated. I am new at this and feeling stuck.

Thanks in advance!

[burningkrome]

Instead of printing the output, you could just store them in a list object. I.e.

valueList = []
for row in ws.iter_rows("L"):
    for cell in row:
        if cell.value == 'Integration Live':
            valueList.append((ws.cell(row=cell.row, column=3).value)) 

Then open the second spreadsheet and do a similar search, checking the values against the items in the list.

for row in ws2.iter_rows("L"):
    for cell in row:
        if cell.value in valueList:
            <do something fun>

[curranjohn46]

Thanks Tim. Makes total sense now. And it worked of course. Appreciate it!!

[curranjohn46]

Another mental hurdle I am trying to get past.

The code below will color the individual cell blue if the value is found in column B. But I am trying to figure out how to color the entire row. I have tried a few approaches to no avail so far. Any tips?

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

for row in ws2.iter_rows(range_string="B"):
    for cell in row:
        if cell.value in subID:
            cell.fill = lightblueFill

[burningkrome]

I found this with a quick search on Stack Overflow. Are you familiar with the Stack Exchanges? They're a great resource too.

https://stackoverflow.com/questions/4298...w-openpyxl

[curranjohn46]

I am familiar with stack exchange. I look everywhere for examples.

I can specify a column and/or row and format it, color it. That is no issue.

My challenge is this: once I iterate through all the rows in a file, then find the value I am looking for in a particular column, how do I format or color that entire row? I can only seem to figure out ways to do it for an individual cell. For example this format one cell in the row but I am trying to figure out how to rewrite it so that the whole row will be colored:

lightblueFill = PatternFill(start_color='c9e1f8',
                   end_color='c9e1f8',
                   fill_type='solid')
 
for row in ws2.iter_rows(range_string="B"):
    for cell in row:
        if cell.value in subID:
            cell.fill = lightblueFill   #####I want to change this line to something like row.fill