Having researched many of the Excel help sites, we have not been able to find a solution for splitting out text in the same cell where a colour changes, or a string is a particular colour within a string. A client required this solution, and after several hours of trialling different situations, we have created the example code below that should give you an idea of how to deal with a brief of this sort.
Our clients project needed to loop down a large list in 1 column of data that had been exported from a language website into Excel and where a colour of the text changed, move it across a cell.
Initially we didn’t think this was possible but after exhausting all avenues of research we have created a very short but particularly neat script to give an example of how this could work. Whilst we haven’t included the full script you should be able to understand the logic of the code and adapt for your own needs whether that be to split text where colours change in the same cell, identify locations in the text where it changes, or count the number of each coloured character.
Feel free to use or adapt the code for your own use or contact us for further assistance. As this is the only solution we had identified we’d be more than happy to help you build your own solution using our example below.
lnth = Len(ActiveSheet.Range("C4"))
For ch = 1 To lnth
If Selection.Characters(ch, 1).Font.Color = 255 Then
ActiveSheet.Range("D4") = Mid(ActiveSheet.Range("C4"), ch, 255)