Can we do conditional formatting in excel based on values in separate Sheet?

Question

I'm trying to do conditional formatting based on the values in the separate sheet. For eg: I have Sheet1 where the table is like this

A   B   C   D   E   F   G   H   I   J
1                                   
2                                   
3                                   
4                                   
5                                   
6                                   
7                                   
8                                   
9                                   
10

And Sheet2 has the same table with values like these,

A   B   C   D   E   F   G   H   I   J
1   1   1   0   0   0   0   0   0   1
2   0   0   1   0   0   0   0   0   0
3   0   0   0   1   0   0   0   0   0
4   0   0   1   0   0   0   1   1   0
5   0   1   0   0   0   1   0   0   1
6   1   0   0   0   0   0   1   0   1
7   0   1   0   0   0   1   0   0   1
8   0   0   1   0   1   0   0   0   1
9   0   0   0   1   0   0   0   0   0
10  0   0   0   0   0   0   0   0   0

Now i want to highlight the cells in Sheet1 for the respective column in Sheet2 where the value is 1.

so you want the sheet 1 to blank always and just reflect the color — Praveen DA
@PraveenDA Its not necessary that the cells are blank, they contain some other data, this is like a template — Godfrey
if that is case the below given answer should work for you.. — Praveen DA

Zac Zac · Accepted Answer · 2018-03-01T08:54:17

You can but I don't understand why you would want to do it that way though. You can follow the steps below:
1. Select the cell (i.e. B1)
2. Click More Rules from Conditional Formatting menu option
3. In New Formatting Rule window, select Use a formula to determine which cells to format
4. In formula bar have the following formula: =Sheet2!$B$1=1
5. Chose a colour to highlight the cell

Repeat the process for each cell

Can we do conditional formatting in excel based on values in separate Sheet?

1 Answers