CODEWITHSUNDEEP
excel
Godfrey
Godfrey

Reputation: 87

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

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.

Upvotes: 0

Views: 33

Answers (1)

Zac
Zac

Reputation: 1944

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

Upvotes: 1

Related Questions