Conditional Formating

[Gazz_85]

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

 

[Eduardo]

Hi,
how do you identify that the job is done, I assume that you have let's say
in cell E1 the word complete and you want cell B1 to became green when job is
complete, so position in cell B1 and apply conditional formating, where the
formula is enter

=E1="Complete"

Hope this help

 

[Gazz_85]

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 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).

any other ideas???

 

[Eduardo]

Hi,
Please let me know how you identify that the unit is complete, is there a
date you have to enter, could you give me an example of the job in progress

 

[Eduardo]

Opps I hit enter before finishing
Job in progress in your spreadsheet

 

[Gazz_85]

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,

 

[Eduardo]

Hi,
Ok so if you want to use it as a whiteboard, select the column where you
want to add the job #, go to conditional formating, select value greater than
enter 0,select the color you want to applyu, now when a value greater than 0
is entered it will highlight in green, when you delete that number cell will
return to the original white format

 

[Gazz_85]

Yeah i know how to do this but what i need is for the unit# number display to
stay as default until the job is completed, then allow it to be changed to
green then reset to default when deleted, to await the next job to be
displayed as default format until thats completed,ect,ect.

 

[Eduardo]

Ok,
So you need a helper column where you enter a "C" for complete and leave it
blank for job in progress, so highlight the column and where the formula is
enter
=B1="C"

 

[Gazz_85]

sound like an option but i have very limit space on the sheet as it is going
to be projected onto screen and all the information needs to be displayed at
nce, and currently i have no room for any more columns.

any other suggestions??

 

[Eduardo]

If you can provide to me with a layout of your sheet with an example I will
be able to help you

 

[Gazz_85]

how do you want me to send it??

If you can provide to me with a layout of your sheet with an example I will
be able to help you

 

[Eduardo]

Just post it for example
column A = job #
Column B = ??
Column E= Calculation = Column A-Column B
etc

 

[Gazz_85]

Col B - Location (number 1 -8 in rows)
Col C - Short term - list of poss short term jobs
Col D - Long term - list of poss long term jobs (n.b. job can either be long
or short term only)
Col E - Unit # - the column for formating
Col F - Safety issues
Col G - Descrip of work
Col H - Job #
Col J - ETS - Estimated Time For Service

so i want the Unit# to dtay defualt when entered, when its ready to be moved
a way to change it green, when its deleted the cell turns back to default,
and when a new number is entered it remains defualt colour again until that
job is complete.

the last bit is were i'm falling short, as the way i'm doing it you can
change it to green, and when del;eted it reverts to default, buit when a new
number is entered instead of remaining default it changes back to green.

Cheers