how do i format text (1, 2.35, 39) with different colors for each number?

[jettabar99]

I have worksheets with lots of cells with 9 numbers separated by commas. An example would be (7, 8.35, 9, 10.5, 11, 12, 14, 18, 24)
The 2nd, 5th and 8th need to be red, the 3rd, 6th and 9th need to be blue and the others black. This can be done during a concatenation of 9 cells or on the cell as is (formula or just copy of values)
I have tried various of things but my knowledge of Excel is limited. I do have some programming experience but not in Excel.

[TMS]

If the cell is using a formula to concatenate several values, you won't be able to do it. If the cell is just a text cell, albeit containing a series of numbers, you could do it manually or with a VBA Macro.



Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[jettabar99]

ok here it is. I don't normally ask for help here so hope I got this right.

[Norie]

I
The 2nd, 5th and 8th need to be red, the 3rd, 6th and 9th need to be blue and the others black.

What's the logic behind which number to colour and which colour to use?

[jettabar99]

Its based on the position only. Its always the 3rd, 6th and 9th as blue and 2nd, 5th and 8th as red - the other 3 black. I have lots of these per project and I have been doing them manually

[Rick Rothstein]

You show Column L as being created with a formula... you cannot format parts of the output from a formula differently from each other... you can only do that with pure text values. To do that, you will need to use a VBA event procedure to create the values in Column L, then that same code can color the output as required. Is it okay for you to use a VBA code in your workbook?

[Norie]

This seems to work.

Please Login or Register  to view this content.

[TMS]

I decided to work this through so I might as well share it. Slightly different approach based on the original CONCATENATE formula.

Please Login or Register  to view this content.

[TMS]

And, if you want the brackets:

Please Login or Register  to view this content.

[jettabar99]

And, if you want the brackets:

Please Login or Register  to view this content.

I feel pretty stupid right about now. How do I run this? I can create a module but I am not sure how to set it up to act on all the rows I need. Sorry for having to ask this - I have been trying to get it going. I have not done any programming in Excel.

[TMS]

There are various ways to run it. You can add the Developer tab to the Ribbon and view and execute the macro from that. Or you could add View Macros to the Quick Access Tab. Or you could create a button and assign the macro to that.

Or you can run it directly from the VBA Editor using the F5 key.

You don't need to make any adjustments to the code. It will operate on any data in row 2 down.

[jettabar99]

Thanks so much. It is working as far as I can tell. My problem seemed to be a break mode issue but now it seems to be working. I will try it on my main sheet now.

Again - thanks everyone for your help!

[TMS]

You're welcome.



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

[Rick Rothstein]

Here is one more macro approach to add to the mix...

Please Login or Register  to view this content.

[TMS]

For completeness, the updated test sample file.