Replace text in certain color

I would like to find all red text in a certain range of cells and replace every red fragment with, let's say, "ABC".

On several forums I found info that it can't be done, because Excel can do it only for full cells with certain formatting, not for formatted fragments.
However, it turned out to be even worse: I tried replacing cells formatted in red as a whole, and Excel still said it finds nothing. But if the formatting is not a color, but, say, bold, then it works.

I also found some VB macros meant to achieve a similar goal, but they were all based on the fact that either the text to be replaced or its position in the cell is known.
In my case I want to replace anything that's red, and I don't know what it can be (an initial/final/middle part of a word or a whole word) and where (at the end/beginning/in the middle of a cell) - it can be anything anywhere.
Red text can occur in a cell once or more, and I want to replace each occurrence.

I don't care what the color of the replaced text will be.

So, e.g.:
text -> texABC
text -> teABCt
text text text -> ABCext ABC text

Is there any way it can be done?

Originally Posted by Indi_Ra

Small change in cantosh code

Apologies. I got careless, then sneaky. My initial procedure in post #2 left out an entire "IF ... THEN" line, which I later edited back in. My post in #2 should be correct now, and my post #4 should cover all of Awijas's examples.

Thank you both. With cantosh's code and a modification based on Indi_Ra's code, I was able to make it work.
The modification was:
For Each x In [C2:C2001]
since the range is fixed, and without it I was getting an error.

There's one more thing I would like to do with this macro before executing the above code: make sure that spaces are not red, and if they are, change their color to auto. How to do it?

I found some suggestions:
http://stackoverflow.com/questions/1...formatted-text
http://excel.tips.net/T002414_Making...nces_Bold.html
But they're full of prompts (of what to change), error messages (nothing found) and other unnecessary stuff. I just need a simple "replace red space with auto-color space".

I added a modification that should change red-fonted blank cells with black ones. I also included the C2:C2001 modification to be better suit your range. Let me know if it does the trick.

Please Login or Register  to view this content.

By spaces I meant spaces between words within one cell, not blank cells, so I think the above is not applicable

Ah, I misunderstood. So you don't want to turn red spaces into "ABC"? To clarify: If you have "test test" in a cell in which only the space between the tests is red, you would prefer "test test" with a black space to "testABCtest"?

No, I want to turn red spaces into black (auto) spaces in order to AVOID turning them into "ABC" by the main part of the macro.

Try this one, see if I've got it now:

Please Login or Register  to view this content.

The spaces are fine now, thanks. But there's another issue I just noticed: it's replacing characters of ANY color other that "auto", not only red...

Originally Posted by awijas

it's replacing characters of ANY color other that "auto", not only red...

Not for me? Can you post a small sample of your workbook so I can try to figure out what you're seeing and why? Be sure to change or remove any sensitive information.

Here it is (should be): http://www.excelforum.com/attachment...1&d=1457625237

Originally Posted by awijas

Here it is (should be): http://www.excelforum.com/attachment...1&d=1457625237

I'm getting an 'Invalid attachment' message. Can you try again?

Sorry for the late reply. I didn't have access to the file either until now. Try this: http://bit.ly/1RaV5d3

Well... that's odd. I downloaded your spreadsheet, and every time I ran the macro on your data, my Excel crashed when it got to C13 (with the green text). If I deleted C13, it ran fine. If I deleted C13 and re-typed the exact same text into C13, green text and all, it ran fine. It left the non-red text alone, as it should. The only time it wouldn't work was on your original data in C13, or your original data in column B copied into column C. It crashed, every time, at C13. Unfortunately, I don't know why. Maybe try creating a completely new set of test data in a different workbook, copying the procedure over, and seeing if you have the same problem? As I said, it's working fine for me on everything but the data set that came with your workbook.

Strange... I'll try, but I just wrote the green text yesterday What Excel version do you have? Mine in Pro Plus 2013

Yeah, I don't understand at all. I have Excel 2010. My Excel had no problem with your red text, just the green. I don't think it's an issue of the shade, either, since the macro ran fine when I typed in the same text in the same cell with what appeared to be the same shade of green. For some reason my Excel couldn't seem to evaluate the color of the original, so it crashed. My procedure is designed to ignore anything that isn't red, so all Excel has to do is register the font as 'non-red'. And yet... crash. Strange.