CODEWITHSUNDEEP
excelconditional-formattingvba
Skippy Fastol
Skippy Fastol

Reputation: 1785

How to make the color of an Excel cell depend on the value of another cell in another sheet?

Here is the deal.

Let's say Sheet 1 contains numerical data over . For instance :

Country Population  GDP  ...
India       V1       V4  ...
Russia      V2       V3  ...
  ...       ...     ...  ...

On sheets 2, 3, ..., n, each country is represented by a cell.

I would like : - Sheet 2 to colorize the various countries depending on Column 2 of Sheet 1 ... - Sheet n to colorize the various countries depending on Column n of Sheet 1

(like a heatmap)

I found links describing how to colorize : - a cell depending on the value it contains (trivial use of Conditional Formatting) - a cell depending on the value of another cell in the same sheet

What I need is a method to colorize a cell depending on the value of a cell in another sheet.

What would be needed to achieve this ? Some VBA or just a few mouse clicks ... ?

Upvotes: 0

Views: 3706

Answers (1)

Excellll
Excellll

Reputation: 5785

One solution to get around the same-sheet restriction on conditional formatting formulas is to name the ranges on the other sheet. You can use references to named ranges in conditional formatting formulas even if the ranges are on another sheet.

So, for example, you could name the column of countries on Sheet1 "Countries", and then name the (same-sized) column of population data "Pops". Then, select your heatmap area on Sheet2 (topleft is at B2 in the following example), and apply a conditional formatting rule using a formula like

=INDEX(Pops,MATCH(B2,Countries,0))>1000000000

and whatever formatting you want applied. This specific example will apply formatting to countries with population greater than 1 billion.

The downside to using conditional formatting in this way is that you will have to define a rule for each color category; you can't use the predefined "continuous" color scales in Excel.

Upvotes: 1

Related Questions