Concatenate values in different columns

Hi,

I am completely new to this forum. Not sure if I am doing this correct.

I have a column (Column 5) which contain cells with strings separated by commas (example: Cell 2: F1, F2, F3, Cell 3: F4, F5, F6 etc). I am trying to find mistakes or duplicate values in these cells.

I took these cells (strings) and separated them in columns and using conditional formatting, It can highlight duplicate strings (if any) in red.

Next, I was trying to concatenate all these columns into the original cell by keeping the format (highlighted color) so the user can see what's has been duplicated.

When I concatenate, the format is lost. I am stuck looking for an answer on this.

I have this so far but the color is not being transfer when performing the macro:

Please Login or Register  to view this content.

ColumnWithCells.png

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however you need to include code tags around your code.

Please take a moment to add the tags. Posting code between tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

Hi and welcome to the forum.

Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

To upload a file click the Go Advanced button at the foot of your post, look underneath the post area for the Manage Attachments section and take it from there. The yellow banner at the top of the forum also explains this.

Please also note when posting code we require you wrap it between CODE tags Administrative Note:

Please see Forum Rule #2 about code tags. I'll change them for you on this occasion since you are new, but please note for the future. More information about these and other tags can be found here

both of these should be copied and pasted into code module1. The Function is courtesy "Code VBA" website.

Please Login or Register  to view this content.

This eliminates the need to use the text to columns and then rejoining the data by concatenating. Just run the code on the original data in column E.

We will still use the Function from Code VBA but the primary macro is modified to color only the font

Please Login or Register  to view this content.