CubaRJ
New Member
- Joined
- Mar 21, 2022
- Messages
- 29
- Office Version
- 365
- 2019
- Platform
- Windows
Hi!
I need help to build a loop that will paint a row in a color to show on duty dates and another loop to paint the off duty.
The idea is to have a button to fire up the color based on user´s settings for OnDuty, OffDuty and Rotations, I have set a minishet for better understanding.
Starting point will always be the Acive Cell the user click, OnDuty will be the numbe rof days a person workm OffDuty the number of days a person rests, rotations will be sum of a full work session plus rest (OnDuty+OffDuty).
If the user click on E5 with setting OnDuty 14, OffDuty 21 and Rotation 9 it would paint the cells as per the example below
Appreciate all the help I can get!
I need help to build a loop that will paint a row in a color to show on duty dates and another loop to paint the off duty.
The idea is to have a button to fire up the color based on user´s settings for OnDuty, OffDuty and Rotations, I have set a minishet for better understanding.
Starting point will always be the Acive Cell the user click, OnDuty will be the numbe rof days a person workm OffDuty the number of days a person rests, rotations will be sum of a full work session plus rest (OnDuty+OffDuty).
If the user click on E5 with setting OnDuty 14, OffDuty 21 and Rotation 9 it would paint the cells as per the example below
- If the user click on E7 with setting OnDuty 14, OffDuty 14 and Rotation 9 it would paint the cells as per the example below
- If the user click on E9 with setting OnDuty 21, OffDuty 21 and Rotation 9 it would paint the cells as per the example below
- If the user click on E11 with setting OnDuty 28, OffDuty 28 and Rotation 9 it would paint the cells as per the example below
Appreciate all the help I can get!
Rotation 14x21.xlsm | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | AA | AB | AC | AD | AE | AF | AG | AH | AI | AJ | AK | AL | AM | AN | AO | AP | AQ | AR | AS | AT | AU | AV | AW | AX | AY | AZ | BA | BB | BC | BD | BE | BF | BG | BH | BI | BJ | BK | BL | BM | BN | BO | BP | BQ | BR | BS | BT | BU | BV | BW | BX | BY | BZ | CA | CB | CC | CD | CE | CF | CG | CH | CI | CJ | CK | CL | CM | CN | CO | CP | CQ | CR | CS | CT | CU | CV | CW | CX | CY | CZ | DA | DB | DC | DD | DE | DF | DG | DH | DI | DJ | DK | DL | DM | DN | DO | DP | DQ | DR | DS | DT | DU | DV | DW | DX | DY | DZ | EA | EB | EC | ED | EE | EF | EG | EH | EI | EJ | EK | EL | EM | EN | EO | |||
1 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 20 | 21 | 22 | 23 | 24 | 25 | 26 | 27 | 28 | 29 | 30 | 31 | 32 | 33 | 34 | 35 | 36 | 37 | 38 | 39 | 40 | 41 | 42 | 43 | 44 | 45 | 46 | 47 | 48 | 49 | 50 | 51 | 52 | 53 | 54 | 55 | 56 | 57 | 58 | 59 | 60 | 61 | 62 | 63 | 64 | 65 | 66 | 67 | 68 | 69 | 70 | 71 | 72 | 73 | 74 | 75 | 76 | 77 | 78 | 79 | 80 | 81 | 82 | 83 | 84 | 85 | 86 | 87 | 88 | 89 | 90 | 91 | 92 | 93 | 94 | 95 | 96 | 97 | 98 | 99 | ### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ||
2 | OnDuty | OffDuty | Rotations | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
3 | 14 | 21 | 2 | SUN | MON | TUE | WED | THU | FRI | SAT | SUN | MON | TUE | WED | THU | FRI | SAT | SUN | MON | TUE | WED | THU | FRI | SAT | SUN | MON | TUE | WED | THU | FRI | SAT | SUN | MON | TUE | WED | THU | FRI | SAT | SUN | MON | TUE | WED | THU | FRI | SAT | SUN | MON | TUE | WED | THU | FRI | SAT | SUN | MON | TUE | WED | THU | FRI | SAT | SUN | MON | TUE | WED | THU | FRI | SAT | SUN | MON | TUE | WED | THU | FRI | SAT | SUN | MON | TUE | WED | THU | FRI | SAT | SUN | MON | TUE | WED | THU | FRI | SAT | SUN | MON | TUE | WED | THU | FRI | SAT | SUN | MON | TUE | WED | THU | FRI | SAT | SUN | MON | TUE | WED | THU | FRI | SAT | SUN | MON | TUE | WED | THU | FRI | SAT | SUN | MON | TUE | WED | THU | FRI | SAT | SUN | MON | TUE | WED | THU | FRI | SAT | SUN | MON | TUE | WED | THU | FRI | SAT | SUN | MON | TUE | WED | THU | FRI | SAT | SUN | |||
4 | 01/05/22 | 02/05/22 | 03/05/22 | 04/05/22 | 05/05/22 | 06/05/22 | 07/05/22 | 08/05/22 | 09/05/22 | 10/05/22 | 11/05/22 | 12/05/22 | 13/05/22 | 14/05/22 | 15/05/22 | 16/05/22 | 17/05/22 | 18/05/22 | 19/05/22 | 20/05/22 | 21/05/22 | 22/05/22 | 23/05/22 | 24/05/22 | 25/05/22 | 26/05/22 | 27/05/22 | 28/05/22 | 29/05/22 | 30/05/22 | 31/05/22 | 01/06/22 | 02/06/22 | 03/06/22 | 04/06/22 | 05/06/22 | 06/06/22 | 07/06/22 | 08/06/22 | 09/06/22 | 10/06/22 | 11/06/22 | 12/06/22 | 13/06/22 | 14/06/22 | 15/06/22 | 16/06/22 | 17/06/22 | 18/06/22 | 19/06/22 | 20/06/22 | 21/06/22 | 22/06/22 | 23/06/22 | 24/06/22 | 25/06/22 | 26/06/22 | 27/06/22 | 28/06/22 | 29/06/22 | 30/06/22 | 01/07/22 | 02/07/22 | 03/07/22 | 04/07/22 | 05/07/22 | 06/07/22 | 07/07/22 | 08/07/22 | 09/07/22 | 10/07/22 | 11/07/22 | 12/07/22 | 13/07/22 | 14/07/22 | 15/07/22 | 16/07/22 | 17/07/22 | 18/07/22 | 19/07/22 | 20/07/22 | 21/07/22 | 22/07/22 | 23/07/22 | 24/07/22 | 25/07/22 | 26/07/22 | 27/07/22 | 28/07/22 | 29/07/22 | 30/07/22 | 31/07/22 | 01/08/22 | 02/08/22 | 03/08/22 | 04/08/22 | 05/08/22 | 06/08/22 | 07/08/22 | 08/08/22 | 09/08/22 | 10/08/22 | 11/08/22 | 12/08/22 | 13/08/22 | 14/08/22 | 15/08/22 | 16/08/22 | 17/08/22 | 18/08/22 | 19/08/22 | 20/08/22 | 21/08/22 | 22/08/22 | 23/08/22 | 24/08/22 | 25/08/22 | 26/08/22 | 27/08/22 | 28/08/22 | 29/08/22 | 30/08/22 | 31/08/22 | 01/09/22 | 02/09/22 | 03/09/22 | 04/09/22 | 05/09/22 | 06/09/22 | 07/09/22 | 08/09/22 | 09/09/22 | 10/09/22 | 11/09/22 | 12/09/22 | 13/09/22 | 14/09/22 | 15/09/22 | 16/09/22 | 17/09/22 | 18/09/22 | ||||||
5 | 14 | 21 | 9 | E | o | o | o | o | o | o | o | o | o | o | o | o | o | D | h | h | h | h | h | h | h | h | h | h | h | h | h | h | h | h | h | h | h | h | h | E | o | o | o | o | o | o | o | o | o | o | o | o | o | D | h | h | h | h | h | h | h | h | h | h | h | h | h | h | h | h | h | h | h | h | h | E | o | o | o | o | o | o | o | o | o | o | o | o | o | D | h | h | h | h | h | h | h | h | h | h | h | h | h | h | h | h | h | h | h | h | h | E | o | o | o | o | o | o | o | o | o | o | o | o | o | D | h | h | h | h | h | h | h | h | h | h | h | h | h | h | h | h | h | h | |||
6 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
7 | 14 | 14 | 9 | E | o | o | o | o | o | o | o | o | o | o | o | o | o | D | h | h | h | h | h | h | h | h | h | h | h | h | h | E | o | o | o | o | o | o | o | o | o | o | o | o | o | E | o | o | o | o | o | o | o | o | o | o | o | o | o | D | h | h | h | h | h | h | h | h | h | h | h | h | h | E | o | o | o | o | o | o | o | o | o | o | o | o | o | E | o | o | o | o | o | o | o | o | o | o | o | o | o | D | h | h | h | h | h | h | h | h | h | h | h | h | h | E | o | o | o | o | o | o | o | o | o | o | o | o | o | E | o | o | o | o | o | o | o | o | o | o | o | o | o | D | |||
8 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
9 | 21 | 21 | 9 | E | o | o | o | o | o | o | o | o | o | o | o | o | o | o | o | o | o | o | o | o | D | h | h | h | h | h | h | h | h | h | h | h | h | h | h | h | h | h | h | h | h | E | o | o | o | o | o | o | o | o | o | o | o | o | o | o | o | o | o | o | o | o | D | h | h | h | h | h | h | h | h | h | h | h | h | h | h | h | h | h | h | h | h | E | o | o | o | o | o | o | o | o | o | o | o | o | o | o | o | o | o | o | o | o | D | h | h | h | h | h | h | h | h | h | h | h | h | h | h | h | h | h | h | h | h | E | o | o | o | o | o | o | o | o | o | o | o | o | o | o | |||
10 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
11 | 28 | 28 | 9 | E | o | o | o | o | o | o | o | o | o | o | o | o | o | o | o | o | o | o | o | o | o | o | o | o | o | o | o | D | h | h | h | h | h | h | h | h | h | h | h | h | h | h | h | h | h | h | h | h | h | h | h | h | h | h | h | E | o | o | o | o | o | o | o | o | o | o | o | o | o | o | o | o | o | o | o | o | o | o | o | o | o | o | o | D | h | h | h | h | h | h | h | h | h | h | h | h | h | h | h | h | h | h | h | h | h | h | h | h | h | h | h | E | o | o | o | o | o | o | o | o | o | o | o | o | o | o | o | o | o | o | o | o | o | o | o | o | o | o | o | D | |||
Planilha1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
E3:EO3 | E3 | =CHOOSE(WEEKDAY(E4,1),"SUN","MON","TUE","WED","THU","FRI","SAT",) |