title: A way to refer to the "Current cell" for a dynamic Conditional Formatting in Google Spreadsheets
body: <p>To be more specific, I want the Conditional Formatting to check the content of the cell that is currently being formatted and be dynamic as I copy-paste the Conditional Formatting into another cell, without having to manually fiddle with the formula again.</p>
  <p>So what I'm doing is this (using an example):</p>
  <p>I have a list of foods categorized by type (Fruits, vegetables, etc...) and every week the list changes, so its not possible to add &quot;hard values&quot; to formulas, it has to be a cell reference under the category.</p>
  <p>Which means that under the category &quot;fruits&quot; for example, in week 1 i can have: banana, apple and peach but totally different fruits another week.</p>
  <p>Anyways, I also want to create a calendar with the days in the week, where I put 4 drop-down menus which correspond to the 4 types of food. The drop down menus update as I update the initial list of avaiable foods.</p>
  <p><strong>Now down to the real issue.</strong></p>
  <p>I want that the cell used for the drop down menu to take an specific color when the content of that cell contains a food inside a given category.</p>
  <p>For example, I select Apple, it checks for the apple and applies the apple color to the cell.</p>
  <p>I acheived this with this</p>
  <p>=COUNTIF(A4:A6, INDIRECT(&quot;RC&quot;,&quot;FALSE&quot;))</p>
  <p><em>I found someone online using the INDIRECT(&quot;RC&quot;,&quot;FALSE&quot;) value to &quot;reference&quot; the current cell but its not working for me...</em></p>
  <p>A4:A6 is the range of fruits and it will give the red color to the apple because i defined it in Conditionnal formatting.</p>
  <p>Now when I copy the conditional formatting is not working for the other apples and I want to make it work for others, just by checking if the current cell contains a value in a range of cells, without manually changing the current cell for every cell.</p>
  <p><a href="https://docs.google.com/spreadsheets/d/15trOcNzucTJDDwuseQTsvjhioIGN9W4NEjhztmMZ1so/edit#gid=0" rel="nofollow noreferrer">https://docs.google.com/spreadsheets/d/15trOcNzucTJDDwuseQTsvjhioIGN9W4NEjhztmMZ1so/edit#gid=0</a></p>
  <p>This is my google spreadsheet, please help ! I'm not sure if I can understand better. This is for a much bigger project and really need the help.</p>