Keep dynamic cell format in concatenation

[ysun]

Hello,

I have a VBA code to concatenate several columns(Column D- Column G) to one column(Column C) and I want to keep original formats and contents. It works well except for numeric value.

For example in my attached worksheet,

Column D is a column for percentages. When I tried to concatenate it to my concatenation column, it lost its format(e.g.50% to 0.5). In my real work, columns will be added. Even if I use codes to format this column(Column D), it will have the same problem after new columns containing percentages are added. I cannot format all numeric value columns to percentages as well because I still have columns that contain normal number(like Column E) The ideal solution will be every time a cell is added to the concatenation, it will keep and original format and content but I don't know how to do it.

Please let me know if any clarification is needed. Any help is appreciated!!!!