I have a spreadsheet that shows the current situation of
25 sales people in an organization, and then finds the
average of certain information. I then show how the
company's situation can be improved by helping salespeople
with below average numbers come up to the average. I'm
using If statements to show improvement if the salesperson
is below average, and how my boss has asked me to have the
spreadsheet automatically show any information that is
changing (i.e. the new figures if a salesperson is brought
up to the average) in green to make changes stand out.

Because an improvement is only made if a person is
currently below average, I can not change the color by
using conditional formatting. Is there a way to
programmatically change the color of the information in a
cell if a certain condition is met?

Tina,

You can also build a regular macro with a loop, or better yet a change event
macro behind the sheet.
Use If statements or Select Case to determine the shading of the cell.

--
sb
"Tina" wrote in message
...
I have a spreadsheet that shows the current situation of
25 sales people in an organization, and then finds the
average of certain information. I then show how the
company's situation can be improved by helping salespeople
with below average numbers come up to the average. I'm
using If statements to show improvement if the salesperson
is below average, and how my boss has asked me to have the
spreadsheet automatically show any information that is
changing (i.e. the new figures if a salesperson is brought
up to the average) in green to make changes stand out.

Because an improvement is only made if a person is
currently below average, I can not change the color by
using conditional formatting. Is there a way to
programmatically change the color of the information in a
cell if a certain condition is met?

Trevor,

The current situation: I have 25 sales people and have
figured their close rate for the month. I then take the
average of all 25 sales people. In the new cell, my If
statement says: if the current close rate for Salesperson1
is less than the average, show the average, otherwise show
the current close rate. If the value in this cell changes
to the average, then I would like the color of the new
number to show in green. I'm creating a sales
presentation tool to show business owners how they can
improve their bottom line by helping their below-average
sales people come up to the average.

Although I've been working with Excel for years, it's been
ages since I've done anything with VB, and then only took
the beginner's training in VB 5.0, so I'm not sure how to
go about doing this. Any help will be GREATLY appreciated.

Tina

Hello Tina,
I currently do a similar process in VBA inside an Access
report. Where I compare last weeks numbers to this weeks
numbers and assign an empty text box a Red, Yellow or Green
background and border color. The color is dependent on how
the number falls. Red for being below (Goal - .05), Yellow
for being around Goal (Goal +/- .05) and Green for
achieving greater than (Goal + .05).

If this sounds like what you are looking for let me know.
I can only post from home due to security issues at work.

Charles.



-----Original Message-----
I have a spreadsheet that shows the current situation of
25 sales people in an organization, and then finds the
average of certain information. I then show how the
company's situation can be improved by helping salespeople
with below average numbers come up to the average. I'm
using If statements to show improvement if the salesperson
is below average, and how my boss has asked me to have the
spreadsheet automatically show any information that is
changing (i.e. the new figures if a salesperson is brought
up to the average) in green to make changes stand out.

Because an improvement is only made if a person is
currently below average, I can not change the color by
using conditional formatting. Is there a way to
programmatically change the color of the information in a
cell if a certain condition is met?
.