Conditional Formatting Using Relative References

Question

Hello All.  

I have a question about copying conditional formatting from one cell to another.  

I have a list of customers that I like to send thank you card to.  In each row I have a drop down box that answers whether or not a card has been sent to that person.  I want the whole row to highlight when it's turned to yes.  

Here's an example of what I have when it's applied to only one row: 

I have read quite a bit on the internet about how to use relative references so formatting can be copied from one cell to another, nothing quite seems to work for me.  

For example, I've tried to apply the advice from this post and this one without any luck.

What I am doing right now is setting up my own rule based on a formula 

that states if =$H$2="yes" then fill w/ color.   Then in the "applies to" section in the rule box select $A$2:$H$:2

This works fine for one row.  But when copied to additional ones only the "Card Sent" box turns blue.  

I've tried different permutations for referencing the cells.  In the posts I linked to it states that using the rule H2="yes" should make the conditional formatting a relative reference but this was not the case for me.  

So far these are the different permutations of the rule I've tried:

Formula:                      Applies To:

=$H$2="yes"               $A$2:$H$2

=H2="yes"                  $A$2:$H$2

=H2="yes"                  A2:H2

=$H$2="yes"               A2:H2

=$H2="yes"                $A$2:$H$2

=$H2="yes"               A2:H2

=$H2="yes"                 $A2:$H2

Please assist.  Thank you.

Answer 1

Hi,

Try this

1. Remove Conditional formatting from the entire worksheet
2. Click on cell A2 and go to Home > Conditional formatting > New Rule > Use a formula to determine which cells to format
3. In the Formula box there, enter this formula

=$H2="Yes"

4. Click on Format and choose your desired formatting
5. Click on OK/Apply
6. Copy cell A2, select range A2:H[last row of data]
7. Right click > Paste Special > Formats > OK

Hope this helps.

Answer 2

You are welcome.

Answer 3

This worked beautifully.  Thank you so much!