Format cells in column only if once cell equals it and another cell equals "Gold"

[kelseyRahenkamp]

I want to format cells based on the value of two other cells. I have a spreadsheet that has company names in one column with their status (silver or gold) in another column. On another sheet, I will list companies out (just certain ones, not all of them) and I want the color to change to green if their level in the other spreadsheet equals gold.

The status will change over time so I will need the second sheet to update when the status on the first sheet for that company changes.

Right now, I have this formula in my conditional formula rule: =AND(Sheet1!$A$2:$A$439=$D$4,Sheet1!$I$2:$I$439="Gold")=TRUE. Simplified, here it is: =AND("company name in sheet 1"="company name in sheet two","status in sheet 1"="Gold")=TRUE

This is not working. What do I need to do to get this to work?

 

[ExcelMercy]

Could you do something like, making a 2nd column to house the status of the company (whether silver/gold) and based on that change the color?

I tend to try to keep conditional formatting to the same sheet.

For instance on your status of the different companies sheet, where the companies and their status are listed:

Excel 2010

	A	B
Company Name	Status
Nike	Silver
Wal-Mart	Gold
Sears	Silver
JCPenney	Gold
Academy Sports	Gold
BassPro	Gold
WalGreens	Silver
Macys	Gold
Target	Silver

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]9[/TD]

[TD="align: center"]10[/TD]

</tbody>

Sheet1​

Your formatted sheet.

In the cells B2:B10:
=IFERROR(INDEX(Sheet1!$B$2:$B$10,MATCH(Sheet2!A2,Sheet1!$A$2:$A$10,0)),"")

Conditional Formatting Rule:
Applies to:
=A2:A10

Format:
Fill: Green

Formula:
=B2:B10="Gold"



Excel 2010

	A	B
Company List:
Academy Sports	Gold
BassPro	Gold
Sears	Silver
Nike	Silver
JCPenney	Gold
Nike	Silver
JCPenney	Gold
Academy Sports	Gold
BassPro	Gold

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]9[/TD]

[TD="align: center"]10[/TD]

</tbody>

Sheet2​

Cells A2, A3, A6, A8, A9, A10 are all green.



You could then hide Column B on Sheet2 if you don't want to see that.