title: Conditional format cells based on match in another sheet
body: <p>I have 2 excel sheets (Using Microsoft excel 2011 for mac, but tried the same on excel 2010 as well)</p>
  
  <pre><code>sheet1(Column D)
  foo
  bar
  
  sheet2
  abc cde foo
  sfd sdf dgf
  bar
  </code></pre>
  
  <p>I have applied 3 different rules using conditional format:<br>
  1. If any of the data in Sheet2 matches with Sheet1(ColumnD) - Make text as blue <br>
  2. If there are any duplicate values in sheet2 - Make text as yellow<br>
  3. If the above 2 are not there - leave it blank<br>
  4. If both Rule 1 and Rule 22 are met - Make text as green <br></p>
  
  <p>What can be Rule 4 ?</p>
  
  <p>Rules:<br></p>
  
  <pre><code> Rule 1 # =ISERROR(MATCH(A1,Sheet1!$D:$D,A1,0))=FALSE
   Rule 2 # Highlight Duplicate values with yellow text
   Rule 3 # =ISBLANK($1:$1048576)
   Rule 4 # (Not sure what to have as formula)
  </code></pre>