Using Excel 2016
Hi,
I have a schedule for my staff that I color code so they know what areas they work each day. It also helps me keep track of full time, seasonal, interns, apprentice, lead and coordinator each day, as well as who is closing or leaving early. Work in one area, you are blue cell. Another is yellow. A third is green. I have some conditional formatting that changes cell color to no fill and text color if I add an :V, :O, :H, :S, :P, or :L to indicate vacation, sick, etc.
What I have: I have workbook with worksheets for each 2 week pay period, named MON ##- MON ##, so "Apr 22- May 5." In cell B9 to O25 (or sometimes 26-30s) on each worksheet are a list of names, sorted by columns for each day for two weeks. So B9:B25 has Sunday, C9:C25 has Monday, etc. Color green (R226,G239,B218) is on top, yellow next (R255,G230,B153), then blue (R217,G225,B242), then no fill. In addition, I have reg black font indicating normal day, bold font indicating closer, italicized indicating leaving early, underlined indicating lead, bold/ital to indicate late closer and a prefix or suffix for temp staff, such as S-Tom, A-Sarah, or Holly-C.
What I do now: sort with 4 levels, font color (auto), then cell color green, then yellow, then blue. My problem is I have to do it per column, I can't do it per sheet or workbook. If I add a new person, I have to resort each column for the entire year, day by day. Multiply this by new seasonals coming and going, people adding vacation, sick day, etc. and it is taking too much time.
What I need: For the cell colors to autosort in each column X9:X25 (or up to 30s) with green on top, then yellow, then blue, then no fill with a macro/VBL.
What I would be great (if I was greedy): To additionally sort in each color section based on font criteria:
1. italicized non-bold font on top
2. reg font
3. bold
4. bold italicized
5. Name-C
6. S-Name
7. I-Name
8. A-Name
For the no fill section, colored font top, reg font bottom.
I am a copy paste VBL editor, a novice, so step by step works better. Here is a photo https://drive.google.com/file/d/1_21eriu0RyNvUQuYiEMyinLLiO7aqIMY/view?usp=sharing, MAY 9 is my ideal sort. But if I can just do the colors, I would be happy. Thanks!
Andy
Hi,
I have a schedule for my staff that I color code so they know what areas they work each day. It also helps me keep track of full time, seasonal, interns, apprentice, lead and coordinator each day, as well as who is closing or leaving early. Work in one area, you are blue cell. Another is yellow. A third is green. I have some conditional formatting that changes cell color to no fill and text color if I add an :V, :O, :H, :S, :P, or :L to indicate vacation, sick, etc.
What I have: I have workbook with worksheets for each 2 week pay period, named MON ##- MON ##, so "Apr 22- May 5." In cell B9 to O25 (or sometimes 26-30s) on each worksheet are a list of names, sorted by columns for each day for two weeks. So B9:B25 has Sunday, C9:C25 has Monday, etc. Color green (R226,G239,B218) is on top, yellow next (R255,G230,B153), then blue (R217,G225,B242), then no fill. In addition, I have reg black font indicating normal day, bold font indicating closer, italicized indicating leaving early, underlined indicating lead, bold/ital to indicate late closer and a prefix or suffix for temp staff, such as S-Tom, A-Sarah, or Holly-C.
What I do now: sort with 4 levels, font color (auto), then cell color green, then yellow, then blue. My problem is I have to do it per column, I can't do it per sheet or workbook. If I add a new person, I have to resort each column for the entire year, day by day. Multiply this by new seasonals coming and going, people adding vacation, sick day, etc. and it is taking too much time.
What I need: For the cell colors to autosort in each column X9:X25 (or up to 30s) with green on top, then yellow, then blue, then no fill with a macro/VBL.
What I would be great (if I was greedy): To additionally sort in each color section based on font criteria:
1. italicized non-bold font on top
2. reg font
3. bold
4. bold italicized
5. Name-C
6. S-Name
7. I-Name
8. A-Name
For the no fill section, colored font top, reg font bottom.
I am a copy paste VBL editor, a novice, so step by step works better. Here is a photo https://drive.google.com/file/d/1_21eriu0RyNvUQuYiEMyinLLiO7aqIMY/view?usp=sharing, MAY 9 is my ideal sort. But if I can just do the colors, I would be happy. Thanks!
Andy
