How can I copy only text of a specific colour in Excel?

Question

I have a worksheet with a large number of cells containing large amounts of text. Within any particular cell there may be some text that is coloured red. I want to remove that text and copy it to a different column.

I have a VBA function that does this by checking the cell contents character by character but the spreadsheet is quite large and the process slows down to a crawl. Is there a more efficient way of doing this?

Answer 1

Your question is a little vague but it looks like you need have your VBA function check the .Interior.Color property of each cell such as Worksheets("yourname").Cells(rowIndex, colIndex).Interior.Color

Answer 2

How exactly are you doing this? Are you activating cells then reading the data in a loop? Have you tried these tips on improving macro performance? If only words (and not specific letters in a given word) are coloured red, could you not create a temporary array out of a block of text (use the space " " as a delimiter) and loop through each word?

Rough ex)

blockOfText = "This is a block of text."
myArray = split(blockOfText, " ")
for each str in myArray
    '' If color is red, append to "bad text array" and remove from this one ''
next
'' Then convert the "myArray" back into a string and place back in the cell