Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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
add comment

3 Answers

Here's one way to do it:

=SUMPRODUCT(1 * IFERROR(FIND("y", $A$1:$D$1), FALSE))

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
add comment

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
add comment

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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.