Trying to append text into a cell with different colours

I’m trying to take a set of data from one sheet, where each row contains a date and a value, and copy the values into the first row of a second sheet ("Output"), where each column represents a date. (Say column A = Jan 1, column B= Jan 2, etc.).

If there are multiple values in the data for the same date, they should appear in the same cell, separated by line-breaks (in the same order that they appear in the data sheet).

Also, depending on some condition about the value, the text in the output sheet should either be red or black.

I’m running into trouble when there are multiple values for the same date, where some are red and some are black.

Here’s the relevant part of my macro:
Assume that I'm looping through the rows of data and that I've selected the text (in a string variable TextValue) and date from a row from the datasheet, and that I've found the appropriate cell to add this value in the Output sheet based on the date. (e.g. if date = Jan 2 then I've selected cell B1)

Please Login or Register  to view this content.

The problem I'm running into is when the colour changes and then there are additional values added afterwards.
For example, if the text should be coloured red-black-black, it ends up red-red-black.
Other examples:
red-black-red ends up red-red-red
red-black-black-black ends up red-red-red-black

It works if there's only one value of a different colour and it's the last one, for example red-black or black-black-red.

It seems like when I try to update the colour the part of the cell that I've added, the colour of the text already in the cell gets set to whatever colour is at the start of the cell.

Any ideas on how to make this work? Thanks in advance!

That explanation was way too confusing, let me simplify and try again:

I've got a cell which has text in it, the text has different colours in it (e.g. of the 6 characters, the first 3 characters are red, the next 3 are black).
I want to append the cell contents with additional text (a line break plus the text in the variable TextValue), and give the new text a new colour (red).

If I do this:

Please Login or Register  to view this content.

Then I lose the black colour of characters 4-6. (I think this happens when I assign the new value to the active cell)

Any ideas on how I can keep the colours of the text when I add the new text?

I also tried to see if I could copy the new text into the clipboard, edit the activecell, and paste, rather than assigning the new value to activecell, but I couldn't get that to work.

I think you need to catenate the text, and then (re)apply the formatting for each section.