how to take colour format of cell by using VSTACK to new sheet

VSTACK returns the values as a dynamic array. It does not bring cell formatting such as fill color or notes/comments.

For a formula result, only the values are returned. If the goal is to keep the source cell color, use one of these approaches instead:

1. Copy and paste manually
   • Create the combined list on a new sheet.
   • Copy each source range and paste it below the previous one.
   • Remove duplicate headers if needed.
   • This preserves formatting because it is a paste operation, not a formula result.
2. Use VSTACK only for values, then sort or format separately
   • Keep the formula for the combined values:

          =SORT(VSTACK(Collective_1!C21#,Collective_2!C21#,Collective_3!C21#,Collective_4!C21#,Collective_5!C21#,Collective_6!C21#,Collective_7!C21#,Collective_8!C21#,Collective_9!C21#),1,-1)
     

   • Then apply formatting again on the new sheet.
   • If the colors are based on rules, use Home > Styles > Conditional Formatting to recreate them.
3. If the colors are used for sorting only
   • Excel can sort by Cell Color, Font Color, or Cell Icon from Data > Sort.
   • This works on formatted cells, but it does not mean VSTACK will transfer the formatting.

If the requirement is to preserve value, format, and note/comment from another sheet, a formula is not enough. Use Copy > Paste Special > All instead.

• VSTACK function
• Combine data from multiple sheets
• Sort data in a range or table in Excel
• Use conditional formatting to highlight information in Excel
• How to reference a cell data with format and note - Microsoft Q&A