Conditional Formatting based on another cell's value ... trying to make like a heat map of numbers

[gmexcel]

Hello,
I am hoping someone can help me figure this one out.

I have a sheet with a list of numbers on it (numbers can be 1-50):

Example:

[TABLE="width: 500"]
<tbody>[TR]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]

and another sheet in the same workbook which I have set with COUNTIF to count the number of times a certain number appears.

Example:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Number[/TD]
[TD]Frequency[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]

I have a conditional format set for the frequency column based on a color scale of red - yellow - green based on percentile. So the cell B4 = red B2 = yellow and B3 = Green.

What I am trying to do is make sort of like a "heat map" of all the numbers like so:

[TABLE="width: 500"]
<tbody>[TR]
[TD]1[/TD]
[TD]5[/TD]
[TD]9[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]6[/TD]
[TD]10[/TD]
[TD]14[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]7[/TD]
[TD]11[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]8[/TD]
[TD]12[/TD]
[TD]16[/TD]
[/TR]
</tbody>[/TABLE]

and make it so that the color of the frequency cell is equal to the color of the number cell.
Kind of visually showing which numbers come up more frequently.

Is there any way I can set one cells color to a color of another cell ?
or
Set a value of a cell to be different than its label? (like setting the number as a variable for the formula attached to the frequency cells)

Thanks in advance!

 

[DILIPandey]

Hi gmexcel,

and make it so that the color of the frequency cell is equal to the color of the number cell.

If the colors are defined in number column, you can use the same color in frequency column or I am missing something here ?


Regards,
DILIPandey