Hi
I am reposting this as I received no solution.
I have conditional formatted cells. I am looking for solution either in formula or VBA.
For Example: For range E5: E25 I wants to see result in E1 Number of blues in this example I have 2 blue
E2: I have Amber : 2
E3l Green = 2
I wants to show results for all other columns but Cell Color in Range E1:P3 should be automatically show similar color under that columns as shown from Range H5:P5 there is only Blue color so it should only show blue but if conditional changes and color changes in in any cell for example for the range H5:P5 if there is green color then it should show green and blue.
Please help as I fund no solution in Google.
Thank you in Advance
I am reposting this as I received no solution.
I have conditional formatted cells. I am looking for solution either in formula or VBA.
For Example: For range E5: E25 I wants to see result in E1 Number of blues in this example I have 2 blue
E2: I have Amber : 2
E3l Green = 2
I wants to show results for all other columns but Cell Color in Range E1:P3 should be automatically show similar color under that columns as shown from Range H5:P5 there is only Blue color so it should only show blue but if conditional changes and color changes in in any cell for example for the range H5:P5 if there is green color then it should show green and blue.
Please help as I fund no solution in Google.
Thank you in Advance
Gantt with dates.xlsm | ||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | |||
1 | KEY | Not Started | 2 | 5 | 5 | 4 | 4 | 4 | 4 | 3 | 2 | 1 | 1 | 1 | Color in Range E1: P3 Need to Autmaticaly Count Color with Associated Color sepeartly as shown for each column | |||||||
2 | InProgress | 1 | 2 | 1 | ||||||||||||||||||
3 | Completed | 2 | 1 | |||||||||||||||||||
4 | Activity Name | Start | Finish | Activity Status | Oct-21 | Nov-21 | Dec-21 | Jan-22 | Feb-22 | Mar-22 | Apr-22 | May-22 | Jun-22 | Jul-22 | Aug-22 | Sep-22 | ||||||
5 | 29-Oct-21 | |||||||||||||||||||||
6 | Task 1 | |||||||||||||||||||||
7 | Tak 1.2 | 02/10/21 | 29/11/21 | Not Started | 02/10/21 | 29/11/21 | ||||||||||||||||
8 | Task 2 | 29/11/21 | InProgress | 29/11/21 | ||||||||||||||||||
9 | Tak 1.3 | 12/10/21 | Completed | 12/10/21 | ||||||||||||||||||
10 | Task 3 | |||||||||||||||||||||
11 | Tak 1.4 | 14/10/21 | 29/12/21 | InProgress | 14/10/21 | 29/12/21 | ||||||||||||||||
12 | Task 4 | 29/11/21 | Not Started | 29/11/21 | ||||||||||||||||||
13 | Tak 1.5 | 29/12/21 | Not Started | 29/12/21 | ||||||||||||||||||
14 | Task 5 | 22/12/21 | 20/02/22 | Not Started | 22/12/21 | 20/02/22 | ||||||||||||||||
15 | Tak 1.6 | 18/11/21 | 15/04/22 | Not Started | 18/11/21 | 15/04/22 | ||||||||||||||||
16 | Task 6 | |||||||||||||||||||||
17 | Tak 1.7 | 29/10/21 | Completed | 29/10/21 | ||||||||||||||||||
18 | Task 7 | 18/12/21 | Completed | 18/12/21 | ||||||||||||||||||
19 | 02-Nov-21 | |||||||||||||||||||||
20 | Task 2 | |||||||||||||||||||||
21 | Task 2.1 | 14/11/21 | 02/09/22 | Not Started | 14/11/21 | 02/09/22 | ||||||||||||||||
22 | Task 3 | 28/03/22 | 02/05/22 | Not Started | 28/03/22 | 02/05/22 | ||||||||||||||||
23 | 04-Nov-21 | |||||||||||||||||||||
24 | Task 3 | |||||||||||||||||||||
25 | Task 3.1 | 23/10/21 | 04/06/22 | Not Started | 23/10/21 | 04/06/22 | ||||||||||||||||
26 | ||||||||||||||||||||||
27 | THE RANGE FROM E5:P25 ARE CONDITIONAL FORMATED | |||||||||||||||||||||
Sheet1 (2) |
Cell Formulas | ||
---|---|---|
Range | Formula | |
E5:P25 | E5 | =IFERROR(IFS(DATE(YEAR($B5),MONTH($B5),1)=E$4,$B5,DATE(YEAR($C5),MONTH($C5),1)=E$4,$C5),"") |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
E5:R25 | Expression | =AND($D5=$B$2,IFS(AND($B5<>"",$C5<>""),AND(E$4>=DATE(YEAR($B5),MONTH($B5),1),E$4<=DATE(YEAR($C5),MONTH($C5),1)),AND($B5="",$C5=""),"",OR($B5="",$C5=""),IF(DATE(YEAR(MAX($B5,$C5)),MONTH(MAX($B5,$C5)),DAY(1))=E$4,TRUE))) | text | NO |
E5:R25 | Expression | =AND($D5=$B$3,IFS(AND($B5<>"",$C5<>""),AND(E$4>=DATE(YEAR($B5),MONTH($B5),1),E$4<=DATE(YEAR($C5),MONTH($C5),1)),AND($B5="",$C5=""),"",OR($B5="",$C5=""),IF(DATE(YEAR(MAX($B5,$C5)),MONTH(MAX($B5,$C5)),DAY(1))=E$4,TRUE))) | text | NO |
E5:R25 | Expression | =AND($D5=$B$1,IFS(AND($B5<>"",$C5<>""),AND(E$4>=DATE(YEAR($B5),MONTH($B5),1),E$4<=DATE(YEAR($C5),MONTH($C5),1)),AND($B5="",$C5=""),"",OR($B5="",$C5=""),IF(DATE(YEAR(MAX($B5,$C5)),MONTH(MAX($B5,$C5)),DAY(1))=E$4,TRUE))) | text | NO |