# Solved: Creating Cell "Cold" And "Hot" Spots Based On Values In Microsoft Excel 2007

Discussion in 'Business Applications' started by blujein, Jun 16, 2010.

Not open for further replies.

Joined:
May 12, 2008
Messages:
158
I am hoping that someone can help me with what may be a complex question.

I have a spreadsheet, similar to the demonstration file I have attached. In it, there are a range of columns. One set of columns (A - D), shows a list of numbers that are references to items in a store, and it also shows their location in the store, so it acts as a floor-plan. The next column (E) lists those items again to show the dollar amount that those items have produced in total sales (F).

So, what I'd like to do is to add a conditional formatting to show me the "cold" and "hot" spots on the floor-plan so I can see where sales are most concentrated. I would assume that this would be done with a colour scale (using the range of values in column F to work itself out)?

Either way, the figures in column F would change weekly, so the relevant cells in columns A - D would need to automatically change when they're matching cells in column E - F change.

I hope this makes sense, as it's difficult to explain, but any help that someone could provide would be greatly appreciated.

#### Attached Files:

• ###### Demonstration File.xlsx
File size:
8.7 KB
Views:
49

Joined:
Mar 27, 2008
Messages:
6,552
First Name:
Hans
If I understand correctly you will update the \$ in column F on a regular basis.

You want the color of the equivalent number in E to light up in the block A-D, right?

What is your criteira for "Hot" and "Cold" and how many varaints to dou want to see?

Joined:
May 12, 2008
Messages:
158

That is correct: I want the cell of the equivalent number in column E to light-up in columns A - D.

The criteria is essentially the colour scale pre-set in the conditional formatting options for Microsoft Office Excel 2007 and above, which would be based off the lowest and highest dollar figures in column E.

I hope that makes sense.

Joined:
Mar 27, 2008
Messages:
6,552
First Name:
Hans
It does make sense, since I don't have Office 2007 here I cannot look into the formats, but maybe you can do it yourself.
What you need is the following:

In cell G1 enther the following formula:
Code:
```=RANK(F1;\$F\$1:\$F\$9)

```
Then you copy the forluma down to row 9;

You will het a ranking based upon the \$ ammojtn in F

Code:
```E       F                G
----------------------------------------------------
9	\$180.999,00	4
22	\$26.990,00	6
98	\$384.888,00	1
132	\$293.001,00	3
145	\$384.211,00	2
176	\$38.490,00	5
284	\$221,00     	9
300	\$2.293,00  	7
854	\$1.223,00  	8

```
Now all you need to figure out is vlookup based upon the values in E and return the value in G to trigger the conditional format
VBA code (macro) would do the job too, only then you need program it to be triggered on change.
If you need help with this just post it.

You've got something to work on for now.

Joined:
May 12, 2008
Messages:
158
When I enter the formula you posted above, it reports: "The formula you typed contains an error."

Am I maybe doing something wrong?

Joined:
Mar 27, 2008
Messages:
6,552
First Name:
Hans
Well, have you looked up the syntax for RANK?
What language are you using. If it's not English then you will have to lookup the equivalent function in your specific language:
For Dutch its RANG,
Once you type the = sign in the cell, you kan open the function list (fx) button in the address bar and then look for it there
I have attached the 2003 version which can open, and the formula will translate itself if you have a different langguage version.

You can always save this one as a 2007 version and it will work.

#### Attached Files:

• ###### bluejein-Demonstration File.xls
File size:
33.5 KB
Views:
42

Joined:
May 12, 2008
Messages:
158
That works for me - thank you for going to that effort!

Could you also walk me through setting up the macro and whatever else I need to get the background colours of the cell range (A - D) dependant on their value?

Joined:
Mar 27, 2008
Messages:
6,552
First Name:
Hans
No problem, will get back to you later today.

Joined:
Mar 27, 2008
Messages:
6,552
First Name:
Hans
Sorry about not getteing back sooner.
Was deep into something else. I'll try to have it on paper and added to the sheet to show you, it's really quite simple but that's with everything, once you know how it works, it's simple.

Bye for now.

Joined:
Mar 27, 2008
Messages:
6,552
First Name:
Hans
See attached version 2003 file.
If you want to use it in 2007 you muast save it as 2007 with macros, the filename will then have the extension xlsm

I think this is what you meant and wanted?

Let me knwo.

Bye.

#### Attached Files:

• ###### bluejein-Demonstration File.xls
File size:
44.5 KB
Views:
48

Joined:
May 12, 2008
Messages:
158
Thanks for getting back to me with that, mate.

That's really close, but I'm a little confused with it.

Essentially, the way the function will actually be used will be to monitor 300 items - not just the 9 that I had listed in my original demonstration file. Can you tell me what needs to change in the code to get that to work? Each item will have its own value, and will need to have a colour (perhaps a colour scale?) to represent it.

Joined:
Mar 27, 2008
Messages:
6,552
First Name:
Hans
This example can be used and all you neeed is increase the range to consider.
How good are you with macro's?

The vba code is all you need to edit.

I can suggest the following oink to explain the colors and it's a good site to find examples
http://www.cpearson.com/excel/colors.aspx

Joined:
May 12, 2008
Messages:
158

I'm not great with macros, no ("not great" is an understatement).

Also...in the workbook that I am working on, there are two sheets - one sheet shows the floor-plan (which I want to "light-up" using the colours), and the second sheet showing the item list, along with the ranks (that you provided the code for).

Joined:
Mar 27, 2008
Messages:
6,552
First Name:
Hans
Two sheets is no problem.
It's just a question of setting up the macro.
If you feel like it, if you want, you can post a sample sheet like the one you'll be working with (no sensitive data if it's there) and I'll try and see if I can give you a basic working example that you can build on.
Macro's aren't that hard, it's just a translation of what you want done to a code.
I imagine it's the end of the day down under, it's 07:46 here and I'll be off to work in 20 minuts but I'll see the messages comming in anyway.

Joined:
Mar 27, 2008
Messages:
6,552
First Name:
Hans
PS The world is small when it comes down to electronic communication.

As Seen On