+ Reply to Thread
Results 1 to 12 of 12

Changing partial format of concatenated cells in VBA

  1. #1
    Registered User
    Join Date
    02-03-2020
    Location
    Spain
    MS-Off Ver
    Office 365
    Posts
    9

    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.
    Attached Files Attached Files

  2. #2
    Forum Expert sweep's Avatar
    Join Date
    04-03-2007
    Location
    Great Sankey, Warrington, UK
    MS-Off Ver
    2003 / 2007 / 2010
    Posts
    3,191

    Re: Changing partial format of concatenated cells in VBA

    Hi,

    Unfortunately, I don't believe that it's possible to do what your asking. The most obvious work around would be to "convert" the concatenated text into standard "typed in" text and then carry out the format.

    Please Login or Register  to view this content.
    Rule 1: Never merge cells
    Rule 2: See rule 1

    "Tomorrow I'm going to be famous. All I need is a tennis racket and a hat".

  3. #3
    Registered User
    Join Date
    02-03-2020
    Location
    Spain
    MS-Off Ver
    Office 365
    Posts
    9

    Re: Changing partial format of concatenated cells in VBA

    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!

  4. #4
    Valued Forum Contributor
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2010
    Posts
    768

    Re: Changing partial format of concatenated cells in VBA

    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.

  5. #5
    Registered User
    Join Date
    02-03-2020
    Location
    Spain
    MS-Off Ver
    Office 365
    Posts
    9

    Re: Changing partial format of concatenated cells in VBA

    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!
    Attached Files Attached Files

  6. #6
    Valued Forum Contributor
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2010
    Posts
    768

    Re: Changing partial format of concatenated cells in VBA

    Quote Originally Posted by mirazojm View Post
    ...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.

  7. #7
    Registered User
    Join Date
    02-03-2020
    Location
    Spain
    MS-Off Ver
    Office 365
    Posts
    9

    Re: Changing partial format of concatenated cells in VBA

    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!
    Attached Files Attached Files

  8. #8
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    UK
    MS-Off Ver
    2016 primarily
    Posts
    3,955

    Re: Changing partial format of concatenated cells in VBA

    The fact remains that you cannot format part of the result of a formula. You would have to convert the formulas to static values.
    Rory
    Days when we raged, we flew off the page
    Such damage was done

  9. #9
    Registered User
    Join Date
    02-03-2020
    Location
    Spain
    MS-Off Ver
    Office 365
    Posts
    9

    Re: Changing partial format of concatenated cells in VBA

    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!

  10. #10
    Valued Forum Contributor
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2010
    Posts
    768

    Re: Changing partial format of concatenated cells in VBA

    Quote Originally Posted by mirazojm View Post
    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?

  11. #11
    Registered User
    Join Date
    02-03-2020
    Location
    Spain
    MS-Off Ver
    Office 365
    Posts
    9

    Re: Changing partial format of concatenated cells in VBA

    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?

  12. #12
    Valued Forum Contributor
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2010
    Posts
    768

    Re: Changing partial format of concatenated cells in VBA

    Quote Originally Posted by mirazojm View Post
    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?
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1