Happy day
Below is the data of my work. Data size will be variable.i need to format the rows and columns as per the values which are intersect.
Rows' colors and formats of column C as shown in the left are as default color and format of the respective same rows in specified working range and Columns' color and format at the top(from F5:N5) are to be copied from the above to the each cell if intersection happens( such that if the cell value at the top (from F6:N6) matches any of the values in the range of same respective row at the right).
Requirement
Step 1:
Format in each row of column C to be copied to respective rows of given specified range.
Step 2 :
Check the each cell value (from F6:N6) in the given range is matching with any of the value in respective range of same row values at the right
Step 3:
If intersect(cell value match with the value in the respective range of rows),respective cell to be formatted with respective column format and color from the top.
I tired with sum product and conditional formatting .it worked for small data. but for large data,processing is taking too much time to give result and also sometime,Laptop get hanged up. Every time,i have to edit my conditional formatting each time when there is change in column and row colors.I tired with google search for vba codes for dynamic ranges. But cant find solution.So,I hope this is right place to share the data and it will be simple for the experts here to give a suitable vba code. i have given the required result of my data.
Below is the data of my work. Data size will be variable.i need to format the rows and columns as per the values which are intersect.
Rows' colors and formats of column C as shown in the left are as default color and format of the respective same rows in specified working range and Columns' color and format at the top(from F5:N5) are to be copied from the above to the each cell if intersection happens( such that if the cell value at the top (from F6:N6) matches any of the values in the range of same respective row at the right).
Requirement
Step 1:
Format in each row of column C to be copied to respective rows of given specified range.
Step 2 :
Check the each cell value (from F6:N6) in the given range is matching with any of the value in respective range of same row values at the right
Step 3:
If intersect(cell value match with the value in the respective range of rows),respective cell to be formatted with respective column format and color from the top.
I tired with sum product and conditional formatting .it worked for small data. but for large data,processing is taking too much time to give result and also sometime,Laptop get hanged up. Every time,i have to edit my conditional formatting each time when there is change in column and row colors.I tired with google search for vba codes for dynamic ranges. But cant find solution.So,I hope this is right place to share the data and it will be simple for the experts here to give a suitable vba code. i have given the required result of my data.
| Formatting data as per intersection.xlsm | ||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | |||
| 5 | ||||||||||||||||||
| 6 | A | B | C | A | B | C | A | B | C | |||||||||
| 7 | A | C | ||||||||||||||||
| 8 | B | |||||||||||||||||
| 9 | A | |||||||||||||||||
| 10 | C | |||||||||||||||||
| 11 | B | C | ||||||||||||||||
| 12 | A | |||||||||||||||||
| 13 | C | |||||||||||||||||
| 14 | A | B | ||||||||||||||||
| 15 | A | |||||||||||||||||
Sheet3 | ||||||||||||||||||
