Functions or commands to edit text generated from Excel.

[Anon_1]

Hi,

The example workbook is structured in this manner: for each row (student) there is one cell containing a longer text about the student's performance on an examination. The longer text in that cell is based on joining together/combining several columns (G to I) of smaller textcomments.

What I want to achieve is that the pieces in the long text should have different colors depending on if it concerns something inadequate, something in need of improvement or something satisfactory (it could be highlighted or different text colors). I also want the comment about something in need of improvement to be cursive and the comment about something inadequate to be in bold text.

It would be preferable if the function or formula is dynamic so if the input values changes then the output also changes.

Is this possible to achieve?

Best regards,

Edit: Workbook changed after error discovered by Richard Buttrey. Exactly all the comments were the same regardless of the number in the table, the error was due to missing $-symbols in the formulas.

[TMS]

Is this possible to achieve?

No. You cannot format parts of a formula's result.

[Richard Buttrey]

When sending a workbook for evaluation you need to ensure it contains a sample of all permutations or combinations of data so that we may test

What you've sent has all the E3:E5 values exactly the same, probably because the IF test in columns G:I always references row 3 rather than the row on which the formula is located.

I'm not going to guess what you want so please correct and add several combinations of data and indicate what formatting each should take. In addition please clarify whether it's just a particular phrase in the text strings that needs formatting or the whole string.

[Anon_1]

Hi Richard,

First of all, thankyou for wanting to help me with this issue. It's poor explanation on my part, the values are the same because each row is a different student that gets assessed on the same criteria.

The comment for each of the three levels of a criteria (A, B or C) is written in the same column. The repetition of values is due to that the comments will be repeated for each student.

What I want is that the different text strings are formatted in different ways. For the level "inadequate" they could be in bold text or red text color (or highlighted red), for the middle level they could be cursive and orange and for the satisfactory level they could be regular format and green text/highlighted green. When the text strings are put together into a longer text it will then contain parts with different formatting.

Is this at all possible to do? Another user said it can't be done.

Again, I'm thankful for your input and help.

Best regards,

Edit: Oh, and depending on if I write a 1, 2 or a 3 in the table that corresponds with a certain comment for that criterion. So 1, 2 or 3 leads to a certain comment for that criterion. There are three criterias A, B and C so each student gets three comments and in the longer text cell the comments are combined into a text.

[6StringJazzer]

It is possible using VBA, but not possible with formulas.

[TMS]

Is this at all possible to do? Another user said it can't be done.

99% sure you can't format parts of the result from a formula, for example, concatenating cells together.

As has been suggested, it might be possible to do something with VBA, but I suspect it would be messy.

[Richard Buttrey]

I would still like to see the stuff I mentioned in #3

[6StringJazzer]

99% sure you can't format parts of the result from a formula

I'm going with 100%.

[Anon_1]

I would still like to see the stuff I mentioned in #3

Thankyou, I've changed the workbook. I didn't check the formulas for selecting which comment that should be shown - the arguments had missing $-symbols which made the formulas incomplete.

[Anon_1]

It is possible using VBA, but not possible with formulas.

Thankyou, I've read up on macros, subroutines and functions it seems complicated.

I guess it would suffice to edit the word "inadequate" in bold and red text color or highlighted red, "lacks certain qualities" in bold and orange text color or highlighted orange and "satisfactory" in bold and green text color or highlighted green. If that could be accomplished it would be great. Also it would be good to show a messagebox with the text "Would you like to format the feedback in bold text and colors?" and the choice Yes or No.

I lack the skills necessary to do it myself but if I had an example of VBA code I could replicate it for other workbooks.

Edit: It is the text from the formula in the column "longer text" that needs to be formatted in this way.

[TMS]

@6SJ:

I'm going with 100%.

I didn't want to appear pedantic

@Anon_1: Thanks for the +rep

[Richard Buttrey]

Thankyou, I've read up on macros, subroutines and functions it seems complicated.

We all thought that once, but investing a little time just studying well written VBA code that comprises mostly standard syntax that uses normal English words would be time well spent if you want to make the most of Excel.

[Anon_1]

We all thought that once, but investing a little time just studying well written VBA code that comprises mostly standard syntax that uses normal English words would be time well spent if you want to make the most of Excel.

Thankyou again, I've begun to study VBA functions for bold text, highlighting and "for each". Should I mark this thread as solved and put a thread up in the forum for "Excel Programming / VBA / Macros"?

[6StringJazzer]

Attached is a VBA solution. Whenever a number in B:D is entered or updated, and all three numbers are present, the text in column E will be populated using the specified fonts.

[Anon_1]

Attached is a VBA solution. Whenever a number in B:D is entered or updated, and all three numbers are present, the text in column E will be populated using the specified fonts.

Thankyou very much, this is the solution I want and need to learn in order to replicate it for other workbooks written for other assignments.

One thing I noted is that the rating for criterion A changes the color of the comment for criterion C, the rating for criterion B changes the color of the comment for criterion A and the rating for criterion C changes the color of the comment for criterion B. Could this issue be fixed?

Edit: I tried to add reputation but there was an error message that I needed to spread it around before adding more to your userprofile.

[6StringJazzer]

I will take a look, might be a few hours before I get to it.

[6StringJazzer]

Corrected version attached.

[Anon_1]

Corrected version attached.

Thankyou very much, I'll spend the day studying this solution and learning to replicate it. I'll return when I have questions.

[Anon_1]

Corrected version attached.

Thankyou very much again for the macro, it works perfectly! I have tried to dissect it in order to understand it but there are several commands or terms that I do not yet understand.

I was wondering if you could and would like to explain the parts I don't grasp?

[6StringJazzer]

Fortunately for you, I am retired so have time, and I like doing this. So I have responded to your questions.

In reading your questions I realized I was remiss in not putting any comments in the code so I have revised it. I also fixed the bug you pointed out.

[Anon_1]

Fortunately for you, I am retired so have time, and I like doing this. So I have responded to your questions.

In reading your questions I realized I was remiss in not putting any comments in the code so I have revised it. I also fixed the bug you pointed out.

Thankyou very much for taking the time to teach me. I began trying to replicate the macro for another worksheet today and I haven’t got it to work yet. I’ll read the attachments and try to learn as much as possible.

[TMS]

Having read 6SJ's response to your queries, I have to say that it is very thorough and clear. That said, if you already understand the topics he talks about, as I do, it is going to be clearer than it may be to a learner. However, it should give you a head start. AND, the best way to learn this stuff is to try, fail, try again, fail again, and then ask specific questions.

If you are having problems with your new project(s), start a new thread, providing sample workbooks and requirements, etc.

[Anon_1]

Having read 6SJ's response to your queries, I have to say that it is very thorough and clear. That said, if you already understand the topics he talks about, as I do, it is going to be clearer than it may be to a learner. However, it should give you a head start. AND, the best way to learn this stuff is to try, fail, try again, fail again, and then ask specific questions.

If you are having problems with your new project(s), start a new thread, providing sample workbooks and requirements, etc.

Hi,

I have never used VBA or any code for Excel before so I had no previous experience or direct understanding of it before today. But I have written several syntax files for IBM SPSS Statistics (although they have been about executing specified commands and then showing the output [i.e. no loops]) so I spent some hours reading 6SJ's subroutine, Googling the terms and tried to understand them and what they do. When I thought I had a grasp of what everything meant and what it did I wrote it down to check my understanding of it. While waiting for a response I tried to adapt and apply 6SJ's code on another (much larger and complex) worksheet, it didn't work at all.

6SJ's clear and thorough answers made me realize several misunderstandings and why my adaption of the subroutine did not work. I now have a very basic understanding of what I'm doing and not solely working in blindness and only guessing what things means and their function. I will mark this thread as solved and take some days to write a functioning subroutine, if it still doesn't work I will start a new thread in the VBA/Macro part of the forum.

Thanks for all the help once again.

[TMS]

Thanks for all the help once again.

You're welcome.