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

#### kelseyRahenkamp

##### New Member
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?

Last edited:

### Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

#### ExcelMercy

##### Board Regular
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
AB
1Company NameStatus
2NikeSilver
3Wal-MartGold
5JCPenneyGold
7BassProGold
8WalGreensSilver
9MacysGold
10TargetSilver

</tbody>
Sheet1

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
AB
1Company List:
Gold
3BassProGold
5NikeSilver
6JCPenneyGold
7NikeSilver
8JCPenneyGold
10BassProGold

</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.

Replies
1
Views
890
Replies
3
Views
1K
Replies
5
Views
373
Replies
10
Views
2K
Replies
8
Views
1K

1,191,008
Messages
5,984,134
Members
439,872
Latest member
ExcelRM

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back