Take the 2-minute tour ×

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".

share|improve this question

3 Answers 3

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.

share|improve this answer

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 $F$3.

share|improve this answer

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:

=COUNTIF($A1:$L1, "y")>0

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 =$A$1:$L$15, the rule will loop through each row in rows 1 to 15 within the columns D to L for the value "y" and if it finds it, it will highlight that row. You'll get something like this:

enter image description here

If you want it to highlight each column of data, instead, change the formula to:

=COUNTIF(A$1:L$1, "y")>0

enter image description here

If you want to highlight the entire range, change the formula to:

=COUNTIF($A$1:$L$15, "y")>0
share|improve this answer

Your Answer


