title: Conditional formatting using AND() function
body: <p>I'm trying conditional formatting on a sheet. I need to fill the cells with a certain color according to the following conditional statement:</p>
  
  <pre><code>=AND((INDIRECT(ADDRESS(4;COLUMN()))&gt;=INDIRECT(ADDRESS(ROW();4)));(INDIRECT(ADDRESS(4;COLUMN()))&lt;=INDIRECT(ADDRESS(ROW();5))))
  </code></pre>
  
  <p>When I try the statements in the AND() function separately, they seem to work, but when I put them together in the function I don't see any formatting happening.</p>
  
  <p>Here is some background:
  Row 4 of the "current column" has a date (DATE1) in it. There are also dates on the D and E columns of the "current row" (DATE2 and DATE3). So, I would like to fill the cell with a color if DATE1 is between DATE2 and DATE3.</p>
  
  <p>I cannot see why the formula is not working. Any help is much appreciated.</p>
  
  <p><strong>Update (Dec 13, 2011):</strong></p>
  
  <p>I implemented a function that I call from the cells I need this functionality. The function returns integer values. Then conditional formatting only uses the integers in the cells. This way, the conditional formatting is less complicated. I'm passing INDIRECT(ADDRESS(ROW();COLUMN())) into the function I implement. So, I have all the information I need when working on relative and/ or absolute cells. Would be great to know a simpler way to pass the current cell as range into the function.</p>
  
  <p>Note: ActiveCell didn't seem to work for me. It uses the data from the cell which is selected by the time the function is run. That's not what I'm looking for. I could of course pass the cell itself (as in A4, B7, etc.) but I'm not sure if it really matters in terms of performance.</p>
  
  <p>Thanks to all of you who responded to my question.</p>