A way to refer to the "Current cell" for a dynamic Conditional Formatting in Google Spreadsheets

Question

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.

So what I'm doing is this (using an example):

I have a list of foods categorized by type (Fruits, vegetables, etc...) and every week the list changes, so its not possible to add "hard values" to formulas, it has to be a cell reference under the category.

Which means that under the category "fruits" for example, in week 1 i can have: banana, apple and peach but totally different fruits another week.

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.

Now down to the real issue.

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.

For example, I select Apple, it checks for the apple and applies the apple color to the cell.

I acheived this with this

=COUNTIF(A4:A6, INDIRECT("RC","FALSE"))

I found someone online using the INDIRECT("RC","FALSE") value to "reference" the current cell but its not working for me...

A4:A6 is the range of fruits and it will give the red color to the apple because i defined it in Conditionnal formatting.

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.

https://docs.google.com/spreadsheets/d/15trOcNzucTJDDwuseQTsvjhioIGN9W4NEjhztmMZ1so/edit#gid=0

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.

Answer 1

Current cell for conditional formatting is left top most cell in Apply to range range. In your case it is D11. So you should use following formula:

=COUNTIF($A$4:$A$6, D11)