Concatenate Values | Retain Conditional Formatting of the Source

So, I've been working on this today.

I was very easily able to build custom concatenate function that automatically brings all the values together with the proper percentage calculations and various delimiters (";" and "/") between the fields.
It was a piece of cake.

However, as I tried to then add in the formatting of the colors, within the same custom function, this proved to be impossible (unless somebody more skilled than I can tackle it).
The problem here is not calculating the applicability of custom formats, or keeping track of the applicable formats. This too is actually very easy.
What is impossible is to apply the formats to the characters before the end of the custom function.
It turns out (as I suspected it would) two things are actually applicable here:
1. Using a custom function places a function in the cell, not the value. Therefore, the representation of the concatenated result is just that, a representation.
2. Even if you could magically tell a function to replace itself with its text result, that text result is NOT in the cell until the function ends. By definition, there would be no opportunity to apply individual character based formats within the function because there is no result until the function actually ends.

However, that does not mean there is no solution here.
It just means the solution needs to be two step.

The first step is to use the custom concatenate function I've written ("ConcatSpecial") to build the unformatted string.
The second step will be to click a button which will then convert all the formula results of the custom function to their text values (PasteSpecial.Values) and then parse through them to apply the custom formats.

So, the reason I provide all this is to ask a question.

Within the sample sheet you've provided, there is no apparent uniform way to programmatically identify where the necessary table will begin or end.

You have two versions of the table, one with a single row and one with multiple rows.
Additionally, you have several blank rows within the lower table.

Programmatically it would be a simple matter to find the top row of the table and keep processing until it hits a blank row.
However, with the inconsistencies in the sample workbook, this "easy way" is not possible.

For purposes of making headway on my day off, I am going to "clean" the sample workbook to what I feel it should look like to be more "technically correct" and proceed.
That is to say, I am going to remove the upper single row table and delete the blank rows from the lower table.

If there is some reason why this would be a problem for your finished result, please let me know via update to this thread at your earliest convenient opportunity.

OK, well, the end result is exactly what you sought, albeit achieved probably different from what you might have expected.

So, for the first part, I wrote a custom function called “ConcatSpecial”
Its arguments look like this in the cell “=ContatSpecial(AJ10:AO10)”
This formula is typed in the home cell and then the 6 columns of data are selected just like any other function
I did NOT write any error handlers for scenarios where you have text where it expects numbers.
I’m assuming you know how and where to get properly expected values to the applicable ranges
This custom function has all the normal functionality as any other built in function (copy/paste,FillDown,etc)

For the second part, once you have the desired people and functions populated, there is a sub associated with the button “Apply Formats”
Clicking the button will pull each person’s values, field by field and compare them against a new table I added on the “Parameters” sheet.
The “Parameters” basically serves the same purpose as the existing conditional formats, but gives you better flexibility than the existing conditional formats.
You now have the ability, if needed, to set different applicable values and color formats for each name.
This includes any regional values you care to add to the table.
The only error handler I built in manages a scenario where you have a name in the main section, but no matching name in the Parameters table.
In this case, it simply tells you in the home cell this person needs parameters.

This is not bomb proof and there are any number of ways it can be broken so I’ll not list them here.
However, if you are partially handy with VBA, even if this does not necessarily exactly suit your needs, there is a good premise from which you can see and test ways to change it.

Enjoy and let me know if you have any questions.

jcanlas,

Just checking back to see if you've downloaded and tested the sample workbook I made.