Swap Specific Text Strings in a Range and Indicate new Strings Not already in Code.

Hi Everyone,

Its been a little while since I've delved into writing/interpreting vba code and I can't quite seem to remember or find a function I'm trying to achieve.

What I want to develop is a macro that will look down a column and replace any cell value that equals "X" with "Y". All values will be text strings.

I'm going to be defining a whole bunch of different X->Y, G->F, etc., and I expect this list to grow over time (I have no issue with modifying the code afterwards, I just can't seem to get the proper syntax for the overall operation). Essentially I would like the macro to look through an entire column and change any X's to Y's, any G's to F's, etc. for each term change I define through an if/then (I'm assuming the if/then is the proper method here).

If possible (bonus function) when new terms arise that are not in my code, I would like the code to indicate them in some way (their cell is highlighted a color or the word "New" appears in an adjacent column in the same row, etc.)

Can anyone help me out? Do you need any more clarity?

Thanks in advance,

-LM

Hi xladept,

Here is a sample workbook. Please let me know if it has sufficient detail to communicate my goal.

A beginner level demonstration as anyone can achieve just using the Macro Recorder :

PHP Code: 

Sub Demo1()
    With ActiveSheet.UsedRange.Columns(1)
        .Replace "Red", "Primary", xlWhole
        .Replace "Yellow", "Primary"
        .Replace "Green", "Secondary"
    End With
End Sub 


Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !

But instead of changing the code each time a word mod is necessary
the better is to use an additional worksheet for listing each inital word and its replacement
and then it would be easier just with an easy formula to highlight any new word …

Another option for part1

Please Login or Register  to view this content.

Or maybe this for both

Please Login or Register  to view this content.