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.

Can someone please tell me how to highlight multiple cells in a row based on some values in any one of a given number of cells?

For example, if in Row 2 any of the cells from D2 to L2 have value "A" or "B" or "C" then I want Row 2 font to be of color Blue.

I am using Microsoft Excel 2008 for Mac.

share|improve this question

2 Answers 2

up vote 2 down vote accepted

You can count the number of occurrences of "A", "B" and "C" in each row using COUNTIF, and then add the results together. If any of those values are present in a row, the sum should return a non-zero value.

Try this:

Go to Conditional Formating > New Rule > Use a formula to determine which cells to format

Enter this formula:

=(COUNTIF($D2:$L2, "A")+COUNTIF($D2:$L2, "B")+COUNTIF($D2:$L2, "C"))>0

Set your format and click OK.
Make sure that the Applies to box in the Conditional Formatting Manager window is set to =$D2:$L2, or the row(s)/range(s) that you wish to apply the conditions to.

These should also work:

=COUNTIF($D2:$L2, "A")+COUNTIF($D2:$L2, "B")+COUNTIF($D2:$L2, "C")

=COUNTIF(2:2, "A")+COUNTIF(2:2, "B")+COUNTIF(2:2, "C")
share|improve this answer

Enter this as your conditional format formula:

=SUMPRODUCT(1 * OR(IFERROR(FIND("A", $D$2:$L$2), FALSE),
                   IFERROR(FIND("B", $D$2:$L$2), FALSE),
                   IFERROR(FIND("C", $D$2:$L$2), FALSE)))

SUMPRODUCT treats the range as an array formula would, checking the FIND on each cell, if it ends up finding even one instance of the letters then it will end up true.

share|improve this answer
1  
If you want to quickly apply this to every row, select the rows and make sure that current cell (the one cell in the selection that's still white) is in row 2. Then type Lance's formula, but leave out the $ before each 2 ($D2:$L2). This will auto adjust for the rest of the rows. –  Hand-E-Food Aug 4 '11 at 23:41
    
Hi Lance, your solution works great! Thanks so much. Unfortunately, I don't understand how it works. Can you please explain? Is this specific to cell formatting? When I type this as a regular formula in a cell, I get "0" regardless of what is in the cells. –  user37473 Aug 5 '11 at 0:19
    
You should get "0" (which equates to FALSE) when none of the letters are in the range. The formula just checks all the cells in the range ($D$2:$L$2) to see if they have that letter. If they do, then the result is a number greater than 0 (which equates to TRUE). –  Lance Roberts Aug 5 '11 at 0:25
    
@Lance: Thanks for your response. However, I do not get a number greater than 0 when I enter your formula as a regular formula in a cell, even if I have letter A in the specified range. That is the reason it is not clear to me why it works with conditional formatting. –  user37473 Aug 5 '11 at 1:22
    
@user37473, well I don't know, I actually tested it first in a cell to make sure I was getting the right result before I put it into a condition format to test. I can't really tell you what's going on without seeing your spreadsheet. –  Lance Roberts Aug 5 '11 at 2:30

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.