Hi,
I am new to these forums and did a search and could not find anything on this. I work in a warehouse w/ 100 employees. I have a Production Sheet that I need to shade cells according to the employees shifts. I have a sheet where we select there schedule time. I just need to shade in the areas they do not work. And grey box in their lunch hours. Any help or referance to any books to help is appreciated.
Production Schedule Sheet Shading
-
-
-
Re: Production Schedule Sheet Shading
Hi and welcome to OzGrid.
Would having Excel do it for you with Conditional Formatting be what you want. You could set the formatting based on up to 3 conditions.
For example, if the cell is empty, fill with grey and place a border around the cell; otherwise fill is none with border.
-
Re: Production Schedule Sheet Shading
I believe it would to but I would need an area say from 5am to 9 shaded in then an area 1pm-130pm grey then after 530 pm shaded as well. Each employee has 16 rows and each column is a 15 minute block for them. I will tinker around with Conditional Formating and see what I can come up with. Thanks.
-
Re: Production Schedule Sheet Shading
Post an example of your workbook
-
Re: Production Schedule Sheet Shading
An example file would be a good idea but from what you say in your 2nd post, the shading isn't really dependent on whether there is anything in the cell but rather what column the cell is in.
So why not just format the appropriate cells. Select the cells, Format>Cells>Pattern and select the colour you want.
Since when you apply a fill/pattern with just the default gridlines, the border disappears. So you may want to put borders on all your cells in the work area or just around the cells with fill/pattern.
-
-
Re: Production Schedule Sheet Shading
I'm also assuming that this is just a visual blocking of time and that there is nothing actually being put into the cells, and that once you decide that a cell should be filled or not, it doesn't change.
If the fill colour changes from one column to another on a weekly basis say, then you may want to do the conditional formatting.
You could do the conditional formatting say for grey, set the condition to if cell is equal to "g" and if true fill and font are grey so the text in the cell doesn't show.
But with Excel's conditional formatting, you are limited to 3 conditions on any one cell; otherwise you have to use a VBA solution.
-
Re: Production Schedule Sheet Shading
Well, I would post an example but the file is to big. I have a page for each day of the week. I also have a Maintainace page where we enter the employee name, Clock Number, Shift and Supervisor. I have it where it automatically updates the Employees names, clock number on the prod sheet. I just need to shade the area needed depending on each shift. I can not use conditional Formating from one worksheet to another so I am down to the VBA aspect. I am doing this to save my Schedulers time to update the sheet instead of having to shade each employees area for there shifts. Each employees area is 13 coulumns and 80 rows. The spreadsheet is rather complicated w/ all the forulas to calculate Productivity Percentage. I am just looking for an ez way to shade for shifts.
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!