I want a Macro to run only if a cell listed in column B says "Red" to create
a seperate spreadsheet.
I currently have 86 line items in this sheet which could potentially go
"Red", so the Macro needs to run each time this happens.

Could be done through event code but we need a few more details.

Your macro will create a new worksheet for potentially up to 86 cells in column
B when the cell value turns to "Red"?

How does the cell get the value "Red"?

Through direct entry or via is it a calculated value?

Would a cell that has a value of "Red" be liable to change to "Blue" then back
to "Red" or stay "Red" once that result has been returned?

i.e. if a worksheet for a cell exists, do not create another one?

Does each new worksheet need to be named according to some convention?


Gord Dibben MS Excel MVP


On Tue, 24 Jul 2007 07:28:01 -0700, deancarpenter
wrote:

I want a Macro to run only if a cell listed in column B says "Red" to create
a seperate spreadsheet.
I currently have 86 line items in this sheet which could potentially go
"Red", so the Macro needs to run each time this happens.

Hi Gord, thanks for your post.

This sheet reports on all my current open installation projects. It is a
Red, Amber, Green, or RAG report to denote if a project is on schedule, close
to going over or is actually over schedule (or budgeted man days). It's a
manual job of making the project Red, Amber or Green.

It's possible that a Red project could return to Green status.

Ideally what I'd like is to create an additional worksheet for each account
manager to highlight where projects relating to their accounts have turned
Red.

Thanks and regards

"Gord Dibben" wrote:

Could be done through event code but we need a few more details.

Your macro will create a new worksheet for potentially up to 86 cells in column
B when the cell value turns to "Red"?

How does the cell get the value "Red"?

Through direct entry or via is it a calculated value?

Would a cell that has a value of "Red" be liable to change to "Blue" then back
to "Red" or stay "Red" once that result has been returned?

i.e. if a worksheet for a cell exists, do not create another one?

Does each new worksheet need to be named according to some convention?


Gord Dibben MS Excel MVP


On Tue, 24 Jul 2007 07:28:01 -0700, deancarpenter
wrote:

I want a Macro to run only if a cell listed in column B says "Red" to create
a seperate spreadsheet.
I currently have 86 line items in this sheet which could potentially go
"Red", so the Macro needs to run each time this happens.

Before making a bunch of new sheets I would think about having all on one sheet.

Use Conditional Formatting to actually turn the cells Red, Green or Amber

Use Autofilters to select a manager's data to see the progress.

You could record a macro whilst doing the Autofilter to copy that manager's data
to a new sheet if need be.

Always easier to work with if all on one sheet.


Gord

On Tue, 24 Jul 2007 23:34:01 -0700, deancarpenter
wrote:

Hi Gord, thanks for your post.

This sheet reports on all my current open installation projects. It is a
Red, Amber, Green, or RAG report to denote if a project is on schedule, close
to going over or is actually over schedule (or budgeted man days). It's a
manual job of making the project Red, Amber or Green.

It's possible that a Red project could return to Green status.

Ideally what I'd like is to create an additional worksheet for each account
manager to highlight where projects relating to their accounts have turned
Red.

Thanks and regards

"Gord Dibben" wrote:

Could be done through event code but we need a few more details.

Your macro will create a new worksheet for potentially up to 86 cells in column
B when the cell value turns to "Red"?

How does the cell get the value "Red"?

Through direct entry or via is it a calculated value?

Would a cell that has a value of "Red" be liable to change to "Blue" then back
to "Red" or stay "Red" once that result has been returned?

i.e. if a worksheet for a cell exists, do not create another one?

Does each new worksheet need to be named according to some convention?


Gord Dibben MS Excel MVP


On Tue, 24 Jul 2007 07:28:01 -0700, deancarpenter
wrote:

I want a Macro to run only if a cell listed in column B says "Red" to create
a seperate spreadsheet.
I currently have 86 line items in this sheet which could potentially go
"Red", so the Macro needs to run each time this happens.

Thanks Gord, I'll give this a go.

"Gord Dibben" wrote:

Before making a bunch of new sheets I would think about having all on one sheet.

Use Conditional Formatting to actually turn the cells Red, Green or Amber

Use Autofilters to select a manager's data to see the progress.

You could record a macro whilst doing the Autofilter to copy that manager's data
to a new sheet if need be.

Always easier to work with if all on one sheet.


Gord

On Tue, 24 Jul 2007 23:34:01 -0700, deancarpenter
wrote:

Hi Gord, thanks for your post.

This sheet reports on all my current open installation projects. It is a
Red, Amber, Green, or RAG report to denote if a project is on schedule, close
to going over or is actually over schedule (or budgeted man days). It's a
manual job of making the project Red, Amber or Green.

It's possible that a Red project could return to Green status.

Ideally what I'd like is to create an additional worksheet for each account
manager to highlight where projects relating to their accounts have turned
Red.

Thanks and regards

"Gord Dibben" wrote:

Could be done through event code but we need a few more details.

Your macro will create a new worksheet for potentially up to 86 cells in column
B when the cell value turns to "Red"?

How does the cell get the value "Red"?

Through direct entry or via is it a calculated value?

Would a cell that has a value of "Red" be liable to change to "Blue" then back
to "Red" or stay "Red" once that result has been returned?

i.e. if a worksheet for a cell exists, do not create another one?

Does each new worksheet need to be named according to some convention?


Gord Dibben MS Excel MVP


On Tue, 24 Jul 2007 07:28:01 -0700, deancarpenter
wrote:

I want a Macro to run only if a cell listed in column B says "Red" to create
a seperate spreadsheet.
I currently have 86 line items in this sheet which could potentially go
"Red", so the Macro needs to run each time this happens.