Hi,

I need to create either a macro or condition formating so that i can change
a field from its defualt to green when a job is done, and back to its defualt
when deleted.

I've had a go at conditional formating and can change it to green(using a
simple macro(recorded changing the fill colour)) and can change it back to
its defualt by adding a conditional formating parameter. the problem is that
when i enter a new value into this field it automatically turns to green, and
i need it to stay as defualt until completed.

any ideas???

regards

How do you want excel to know that the job is completed..

1. Do you have a completion date ?
If so use ConditionalFormattingCondition1Formula Is
=A1=TODAY()

2. Do you have a status column where once the job is finished you enter
'Done' or something.
If so use ConditionalFormattingCondition1Cell Value isEqual to "Done"

If this post helps click Yes
---------------
Jacob Skaria


"Gazz_85" wrote:

Hi,

I need to create either a macro or condition formating so that i can change
a field from its defualt to green when a job is done, and back to its defualt
when deleted.

I've had a go at conditional formating and can change it to green(using a
simple macro(recorded changing the fill colour)) and can change it back to
its defualt by adding a conditional formating parameter. the problem is that
when i enter a new value into this field it automatically turns to green, and
i need it to stay as defualt until completed.

any ideas???

regards

not to sure this help really.

what i have is a list of 8 cells (representing work areas) each cell has a
current job identified by unit # when work is ongoing i want the cells to
have defualt formatting. once a job is complete i need it to display the
unit# with a green background format so the workers will knoe that the job is
complete and the unit can be moved (can take a while to move). once the unit
has been moved the unit number is deleted from that cell (work area) and the
format returns to default. allowing that work area to be used for another
job.


the only way used to identify a job is done is whenits actually been done and
a worker updates the sheet. theres not set time,ect. the current metod used
is a manual whiteboard, that the workers write on with marker pen. were
changing this for a digital excel version so want to keep it as simple as
possible. so click a command button, or automatically returns to defualt. the
guys using this sheet are very low level computer competant so simplicity is
key.

thanks,

the problem i have is i can set a defualt format, can change it the green
when job complete and can set a condition for the cell to revert to defualt
settings once deleted. then the problem is when the next unit# is inputted
the format automatically is set to green.

the way i did it was to set defiualt format, to change it to green using a
recorded macro assigned to a command button, and revert it back to default on
the condition that the cell value was less then 1.(all unit# are 6 digits
between 300000 - 500000).



"Jacob Skaria" wrote:

How do you want excel to know that the job is completed..

1. Do you have a completion date ?
If so use ConditionalFormattingCondition1Formula Is
=A1=TODAY()

2. Do you have a status column where once the job is finished you enter
'Done' or something.
If so use ConditionalFormattingCondition1Cell Value isEqual to "Done"

If this post helps click Yes
---------------
Jacob Skaria


"Gazz_85" wrote:

Hi,

I need to create either a macro or condition formating so that i can change
a field from its defualt to green when a job is done, and back to its defualt
when deleted.

I've had a go at conditional formating and can change it to green(using a
simple macro(recorded changing the fill colour)) and can change it back to
its defualt by adding a conditional formating parameter. the problem is that
when i enter a new value into this field it automatically turns to green, and
i need it to stay as defualt until completed.

any ideas???

regards

You can probably arrange the data as below in 3 columns and apply conditional
formatting to Column C. Select Column C. FormatConditional Formatting

Condition1CellValueIsEqual toCompleted. Select green color.
Condition2CellValueIsEqual toIn Progress. Select another color to denote
work in progress.

The status column in C can be a drop down so that the users need not type
in. Select Column C. From menu DataValidationSelect ListIn Source enter
the below and hit OK
In Progress,Completed,Free

You can change the status in ColumnC. Once the work is over they can select
Completed which will assign the green color. Once the unit is moved you can
remove the unit number and select Free in Column C

ColA ColB ColC
WorkArea Unit Number Status
WorkArea1 300001 In Progress
WorkArea2 300002 In Progress
WorkArea3 300003 In Progress
WorkArea4 Free
WorkArea5 Free
WorkArea6 Completed
WorkArea7 300007 In Progress
WorkArea8 300008 In Progress

If this post helps click Yes
---------------
Jacob Skaria


"Gazz_85" wrote:

not to sure this help really.

what i have is a list of 8 cells (representing work areas) each cell has a
current job identified by unit # when work is ongoing i want the cells to
have defualt formatting. once a job is complete i need it to display the
unit# with a green background format so the workers will knoe that the job is
complete and the unit can be moved (can take a while to move). once the unit
has been moved the unit number is deleted from that cell (work area) and the
format returns to default. allowing that work area to be used for another
job.


the only way used to identify a job is done is whenits actually been done and
a worker updates the sheet. theres not set time,ect. the current metod used
is a manual whiteboard, that the workers write on with marker pen. were
changing this for a digital excel version so want to keep it as simple as
possible. so click a command button, or automatically returns to defualt. the
guys using this sheet are very low level computer competant so simplicity is
key.

thanks,

the problem i have is i can set a defualt format, can change it the green
when job complete and can set a condition for the cell to revert to defualt
settings once deleted. then the problem is when the next unit# is inputted
the format automatically is set to green.

the way i did it was to set defiualt format, to change it to green using a
recorded macro assigned to a command button, and revert it back to default on
the condition that the cell value was less then 1.(all unit# are 6 digits
between 300000 - 500000).



"Jacob Skaria" wrote:

How do you want excel to know that the job is completed..

1. Do you have a completion date ?
If so use ConditionalFormattingCondition1Formula Is
=A1=TODAY()

2. Do you have a status column where once the job is finished you enter
'Done' or something.
If so use ConditionalFormattingCondition1Cell Value isEqual to "Done"

If this post helps click Yes
---------------
Jacob Skaria


"Gazz_85" wrote:

Hi,

I need to create either a macro or condition formating so that i can change
a field from its defualt to green when a job is done, and back to its defualt
when deleted.

I've had a go at conditional formating and can change it to green(using a
simple macro(recorded changing the fill colour)) and can change it back to
its defualt by adding a conditional formating parameter. the problem is that
when i enter a new value into this field it automatically turns to green, and
i need it to stay as defualt until completed.

any ideas???

regards

the only issue with this is thst i'd have to add a column for the in
progrees,complete,etc and the space on the display is at maximuim as it is.
is there a more copmplex way around this possibly??

what i tried way to have a command button that when presses changed the fill
colour of that cell to green, then set a condition so that when the cell
value was less then 1 change it back to defualt colour for when its deleted
which works, but the problem is when i enter a new unit # it automatically
reverts back to green, not defualt colour!

"Jacob Skaria" wrote:

You can probably arrange the data as below in 3 columns and apply conditional
formatting to Column C. Select Column C. FormatConditional Formatting

Condition1CellValueIsEqual toCompleted. Select green color.
Condition2CellValueIsEqual toIn Progress. Select another color to denote
work in progress.

The status column in C can be a drop down so that the users need not type
in. Select Column C. From menu DataValidationSelect ListIn Source enter
the below and hit OK
In Progress,Completed,Free

You can change the status in ColumnC. Once the work is over they can select
Completed which will assign the green color. Once the unit is moved you can
remove the unit number and select Free in Column C

ColA ColB ColC
WorkArea Unit Number Status
WorkArea1 300001 In Progress
WorkArea2 300002 In Progress
WorkArea3 300003 In Progress
WorkArea4 Free
WorkArea5 Free
WorkArea6 Completed
WorkArea7 300007 In Progress
WorkArea8 300008 In Progress

If this post helps click Yes
---------------
Jacob Skaria


"Gazz_85" wrote:

not to sure this help really.

what i have is a list of 8 cells (representing work areas) each cell has a
current job identified by unit # when work is ongoing i want the cells to
have defualt formatting. once a job is complete i need it to display the
unit# with a green background format so the workers will knoe that the job is
complete and the unit can be moved (can take a while to move). once the unit
has been moved the unit number is deleted from that cell (work area) and the
format returns to default. allowing that work area to be used for another
job.


the only way used to identify a job is done is whenits actually been done and
a worker updates the sheet. theres not set time,ect. the current metod used
is a manual whiteboard, that the workers write on with marker pen. were
changing this for a digital excel version so want to keep it as simple as
possible. so click a command button, or automatically returns to defualt. the
guys using this sheet are very low level computer competant so simplicity is
key.

thanks,

the problem i have is i can set a defualt format, can change it the green
when job complete and can set a condition for the cell to revert to defualt
settings once deleted. then the problem is when the next unit# is inputted
the format automatically is set to green.

the way i did it was to set defiualt format, to change it to green using a
recorded macro assigned to a command button, and revert it back to default on
the condition that the cell value was less then 1.(all unit# are 6 digits
between 300000 - 500000).



"Jacob Skaria" wrote:

How do you want excel to know that the job is completed..

1. Do you have a completion date ?
If so use ConditionalFormattingCondition1Formula Is
=A1=TODAY()

2. Do you have a status column where once the job is finished you enter
'Done' or something.
If so use ConditionalFormattingCondition1Cell Value isEqual to "Done"

If this post helps click Yes
---------------
Jacob Skaria


"Gazz_85" wrote:

Hi,

I need to create either a macro or condition formating so that i can change
a field from its defualt to green when a job is done, and back to its defualt
when deleted.

I've had a go at conditional formating and can change it to green(using a
simple macro(recorded changing the fill colour)) and can change it back to
its defualt by adding a conditional formating parameter. the problem is that
when i enter a new value into this field it automatically turns to green, and
i need it to stay as defualt until completed.

any ideas???

regards