Conditional Formatting

[Santurn]

I need help. I am looking for a formula to format Column A with shading based on a value found in Columns D, G and H. The value or text I am looking for is a "yes" in Columns D, G and H. Once that value is found I need the customer's name in Column A to be shaded. I have a formula, i.e., =D2:D25="Yes" (select format color), that works for one column but wanted to know if there is a nested formula I can use to attain the same result.

Any help is appreciated.

 

[MDCurry]

Assuming you are on row 16 of your data. In cell A16 enter a conditional formula that:
"Use a formula to determine which cells to format"
enter the formula below:

=IF(AND(D16="Yes",G16="Yes",H16="Yes"),TRUE,FALSE)

And format as you wish.

 

[Santurn]

Thank you for replying. My apologies but I need to add a little more information. I have a cell range. I modified your formula this way: =IF(AND(D2:D69="Yes",G2:G69="Yes",H2:H69="Yes"),TRUE,FALSE)

I highlighted row B2:B69 and created the formula but it did not work. I show a color for shading column B when a Yes was found in columns D, G, and H but it did not shade any of the cells in B where there was a Yes found.

 

[T. Valko]

Thank you for replying. My apologies but I need to add a little more information. I have a cell range. I modified your formula this way: =IF(AND(D2:D69="Yes",G2:G69="Yes",H2:H69="Yes"),TRUE,FALSE)

I highlighted row B2:B69 and created the formula but it did not work. I show a color for shading column B when a Yes was found in columns D, G, and H but it did not shade any of the cells in B where there was a Yes found.

Do ALL 3 cells in the same row need to contain Yes or does ANY cell in the row need to contain Yes?

What version of Excel are you using?

 

[Santurn]

No, they do not need to say Yes. If Yes appears in any of the three columns I need to shade the cell in B that corresponds to the yes that appears. Some of the cells contains pending and N/A -- I'm just looking for the Yes. I am using Excel 2007. Here's a crude example:


A B C D E F
9 Butler, Keith G 35.100 Yes 1/1/2005-5/31/2008 (ECA) Duke Inclusionary (ECA)

G H I J K L M
N/A Yes 0.000 35.100 Yes 1/1/2005-5/31/2008 (ECA) Duke

 

[Santurn]

My example doesn't appear correctly but each of the items listed are a column -- all of the column headings seem to have clumped together.

 

[T. Valko]

My example doesn't appear correctly but each of the items listed are a column -- all of the column headings seem to have clumped together.

Try this...

Let's assume the range to format is B2:B10.

Select the *entire* range B2:B10 starting from cell B2. Cell B2 will be the
active cell. The active cell is the one cell in the selected range that is not shaded. The formula will be relative to the active cell.

• Goto the Home tab>Styles>Conditional Formatting>Manage rules>New rule>Use a formula to determine which cells to format
• Enter this formula in the box below:
• =OR(D2="yes",G2:H2="yes")
• Click the Format button
• Select the desired style(s)
• OK out

 

[Santurn]

That worked beautifully!!!! Thank you so much.

 

[T. Valko]

That worked beautifully!!!! Thank you so much.

You're welcome. Thanks for the feedback!

 

[Santurn]

I modified the rule to pick up all of my rows and it's not working now. How do I pick up all 69 rows?

=OR(D2="yes",G2:H69="yes") I added the H69 and it didn't work.