How do I reference another cell in my conditional formatting?
I want an entire range to highlight in green if a particular cell in that range is populated with "y".
Here's one way to do it:
Just change your range to suit. SUMPRODUCT is a non-array formula that treats the range as an array, so it does the FIND on each cell in the range. If any of them contain a "y", then it ends up with a non-zero value, and the condition happens.
You can force a formula or rule to always reflect a certain column or row. Simply put a $ (dollar sign) before the column or row. To always reflect a specific cell, put a $ before the column and row. For example, to reference cell F3, use
This question is a little similar to this.
For your specific problem, you can try this:
Go to Conditional Formating > New Rule > Use a formula to determine which cells to format
Enter this formula:
Set your format and click OK.
Make sure that the Applies to box in the Conditional Formatting Manager window is set to the range(s) that you wish to apply the condition to.
For this example, if you set the Applies to field to
If you want it to highlight each column of data, instead, change the formula to:
If you want to highlight the entire range, change the formula to: