title: Search a word in a text string in Excel sheet using openpyxl
body: <p>I'm trying to search for a word in a cell that has a text string that looks like this  (Energy;Green Buildings;High Performance Buildings). Here is the code I wrote, I get a syntax error</p>
  
  <pre><code>for row in ws.iter_rows('D2:D11'):
      for cell in row:
          if 'Energy' in ws.cell.value :
              Print 'yes'
  </code></pre>
  
  <p>Obviously, I don't want to print yes, this was to test the search function.</p>
  
  <p>Additionally, I want to get the cell location, and then tell openpyxl to assign a color to a cell in the same row under column E. here is a snap shot of my Excel sheet.
  I know how to assign a color using this command</p>
  
  <p><code>c.fill = PatternFill(start_color='FFFFE0', end_color='FFFFE0' 
  fill_type='solid'</code>)</p>
  
  <p>I just need help getting the cell location (the cell that has a matching text) and assign its row number to another cell in column E</p>
  
  <p><a href="https://i.stack.imgur.com/osYZG.jpg" rel="noreferrer"><img src="https://i.stack.imgur.com/osYZG.jpg" alt="enter image description here"></a></p>
  
  <p>UPDATE: I wrote this code below that is working fine for me:</p>
  
  <pre><code>import xml.etree.ElementTree as ET
  
  
  
  fhand = open ('My_Collection')    
  tree =ET.parse('My_Collection.xml')
  data= fhand.read()
  root = tree.getroot()
  tree = ET.fromstring(data)
  
  title_list= ['Title']
  year_list = ['Year']
  author_list= ['Author']
  label_list = ['Label']
  
  
  
  for child in tree:
      for children in child:
          if children.find('.//title')is None :
              t='N'
          else:
              t=children.find('.//title').text
          title_list.append(t)
      print title_list
      print len(title_list)
  
  
  for child in tree:
      for children in child:
          if children.find('.//year')is None :
              y='N'
          else:
              y=children.find('.//year').text
          year_list.append(y)
      print year_list
      print len(year_list)
  
  
  for child in tree:
      for children in child:
          if children.find('.//author')is None :
              a='N'
          else:
              a=children.find('.//author').text
          author_list.append(a)
      print author_list
      print len(author_list)
  
  
  for child in tree:
      for children in child:
          if children.find('label')is None :
              l='N'
          else:
              l=children.find('label').text
          label_list.append(l)
      print label_list
  print len(author_list) 
  
  
  
  
  
  Modified_label_list=list()        
  import re
  for labels in label_list:
  
      all_labels=labels.split(';')
  
      for a_l in all_labels:
          if a_l not in  Modified_label_list: 
              Modified_label_list.append(a_l)
          else:
              continue
  print Modified_label_list
  print len(Modified_label_list)
  label_list_for_col_header= Modified_label_list[1:]
  print label_list_for_col_header
  print len(label_list_for_col_header)
  
  
  
  
  from openpyxl import Workbook 
  wb = Workbook() 
  ws = wb.active 
  
  
  for row in zip(title_list, year_list, author_list, label_list): 
          ws.append(row)
  
  
  
  
  r = 5
  for N in label_list_for_col_header:
      ws.cell(row=1, column=r).value = str(N)
      r += 1
  
  
  from openpyxl.styles import PatternFill 
  
  
  general_lst= list()
  
  
  
  COLOR_INDEX = ['FF000000', 'FFFFFFFF', 'FFFF0000', 'FF00FF00', 'FF0000FF',
                 'FFFFFF00', 'FFFF00FF', 'FF00FFFF', 'FF800000', 'FF008000', 'FF000080',
                 'FF808000', 'FF800080', 'FF008080', 'FFC0C0C0', 'FF808080', 'FF9999FF',
                 'FF993366', 'FFFFFFCC', 'FFCCFFFF', 'FF660066', 'FFFF8080', 'FF0066CC',
                 'FFCCCCFF', 'FF000080', 'FFFF00FF', 'FFFFFF00', 'FF00FFFF', 'FF800080',
                 'FF800000', 'FF008080', 'FF0000FF', 'FF00CCFF', 'FFCCFFFF', 'FFCCFFCC',
                 'FFFFFF99', 'FF99CCFF', 'FFFF99CC', 'FFCC99FF', 'FFFFCC99', 'FF3366FF',
                 'FF33CCCC', 'FF99CC00', 'FFFFCC00', 'FFFF9900', 'FFFF6600', 'FF666699',
                 'FF969696', 'FF003366', 'FF339966', 'FF003300', 'FF333300', 'FF993300',
                 'FF993366', 'FF333399', 'FF333333']
  
  import random
  color_lst= random.sample(COLOR_INDEX, len(label_list_for_col_header))
  print color_lst
  
  print int(label_list_for_col_header.index(label_list_for_col_header[0]))
  
  h= len(title_list)
  m= 0    
  for lbls in label_list_for_col_header: 
      j= int(label_list_for_col_header.index(lbls))+5
      for row in ws.iter_rows('D2:D11'):
          for cell in  row:
  
              if lbls in cell.value : 
                  general_lst.append(cell.row)
                  for items in range(len(general_lst)):
  
                      ws.cell(row = general_lst[items], column = j).fill = PatternFill(start_color=str(color_lst[m]), end_color=str(color_lst[m]) , fill_type='solid')
      general_lst = []
      m +=1       
  
  
  ws.column_dimensions['A'].width = 70    
  ws.column_dimensions['C'].width = 23
  ws.column_dimensions['B'].width = 5        
  wb.save("Test61.xlsx")      
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/98kJI.jpg" rel="noreferrer"><img src="https://i.stack.imgur.com/98kJI.jpg" alt="enter image description here"></a>   </p>