Changing partial format of concatenated cells in VBA

Hi,

I have the following problem:

In a workbook, I have text cells which are a result of concatenating the text in two different columns. In the sample workbook, these are cells D3:D6.
In the adjacent column, I have text cells which are the exact same text, but directly input by typing in each cell. In the sample workbook, these are cells E3:E6.
Column F contains a check to verify that the content of both columns is the same for each row.

Now my problem. I want to change the font of the text but only for the first three characters (the numbers). To this end, I have employed the following VBA code:

Please Login or Register  to view this content.

When the code is applied to the concatenated cells (column D) it changes the WHOLE cell to bold.
When the code is applied to the directly typed-in cells (column E) it changes the correct characters (just the first three).

Obviously, my problem is that I need to perform this action on a large number of cells and therefore need to use VBA code, and the cells are all results of CONCATENATE functions.

In enclose the workbook for clarity.

I think the selection of characters bit and changing the format is OK (I did Debug.Prints and they look fine). It might have something to do with the way VBA interprets text from CONCATENATE function (as different to directly typed in text).

Any thoughts will be greatly appreciated. THANKS IN ADVANCE.

Hi sweep and thanks for your fast reply.

Unfortunately your solution works only for a one-off situation, but I need to maintain the special cell format (first three characters bold) AND the concatenate formula, as the values in the cells will be changing based on the contents of the cells they are referencing in the first place.

Any other ideas? Thanks in advance!

You cannot format the parts of text returned from a formula differently from each other. The work-around would be to remove the formulas and implement event code that duplicates the results from the formulas, then format its output as desired. In order to do this, we would need to see the actual formula being used and the range of cells it should operate over. You posted file looks like a made-up simplified example which would mean any code we developed would not work against your real data. If you could post a real sample file (obfuscate any sensitive data), we would be happy to try and develop an event code procedure for you.

Hi Rick and thank you too for your interest.

The workook I sent really is pretty much real - i just reduced the number of cells. I enclose another one to better illustrate the calculations.

In the first sheet, I calculate these "concatenated" text values for the names of an "operation".
I then assign a "resource" to each operation for a range of "dates".
In the other sheet, I index/match the name of the "resource" with the same range of "dates" to give me the "operation" that each resource is working on for any given date.
And it's on these values (the index/matched concatenated "operations") that I need to perform the partial change of font style on.

I have intentionally typed in the last cell value (700 operation 100) to check and to my surprise - it still does the same and formats the COMPLETE string! So, the problem is NOT from the "concatenate" operation, it's from the index/match or lookup function and the nature of the text it returns and how the VBA formats it.

Hope this helps to illustrate the scenario better.

THANK YOU!

Originally Posted by mirazojm

...it's from the index/match or lookup function...

What index/match or lookup function? The file you posted only has a concatenate function in it. We need to see your actual file with all interconnected formulas.

Hi,

Apologies, for some reason during my checking phase the formulae must have been "stepped on" by VBA. The INDEX/MATCH formulae are now back in the appropriate sheet.

Thanks!

Thanks for your feedback rorya. It seems pretty clear from all your opinions that I cannot accomplish the text editing that I want.

Thanks anyway!

Originally Posted by mirazojm

It seems pretty clear from all your opinions that I cannot accomplish the text editing that I want.

I am still wondering if event code might work for you. In order to know for sure, I need to know how your workbook is used. I am guessing that there is no user interaction at all on the "Lookup and Format Change" sheet... is that correct? Would I be correct in assuming that on the "Concatenation" sheet that Columns B, C and D are pre-filled out and that at some later time you assign Resources, one-at-a-time, against those ID-Operations on a per date basis? If so, then you only need code which automatically fills in the "Lookup and Format Change" sheet with a formatted item every time a resource is placed in Columns E through N (or whatever the last column is at the time)... is that correct?

Hi Rick,
Correct, no user interaction in the “lookup” sheet. It is just filled out automatically when I change data on the “concatenation” sheet.
And correct, on that sheet, columns B, C and D are prefilled and I assign resources to each id-operation for each date one at a time. So yes, you are correct in all your assumptions.

Is there still hope?

Originally Posted by mirazojm

Hi Rick,

Is there still hope?

I have attached a sample worksheet which contains the event code that implements the idea I have about how to do what you want. Note that there are NO formulas on the "Lookup and Format Change" sheet... the event code will take care of filling those in with the first three characters bolded. So, to use this workbook, simply fill in the resource (for example, Resource 5) in any cell within the table range (cells E3:N102) on the "Concatenation" sheet, then go to the "Lookup and Format Change" sheet to see what the event code did. Is this what you wanted?