Find string and set value using both and/or RegEx

[Brutusar]

Hi, I am trying to write a code as described below, but I am not sure how to do it best. (I have already been scrapping a couple of alternatives I made)

The code should loop thru a column looking for a string. This may be the only string in that column, or a substring. (for instance a word in a sentence).
Depending on the string, if it is found it should set a value in one or two different columns. Wither one of thes may be blank. When the searched value is found the rows background color should change to green
to show that this row is processed successfully.

Then the second step. It may be that it is necessary to use RegEx to find the value. This may mainly be because the searched value is not written in full, but a abbrevitaion has been used. Should this be done in two different Subs or
will a "all-in-one" solution be best? ("If not found then run second sub"

Does anybody has any suggestion on how to do this best?

The example below uses col ABC, and the string to be found is "abbreviation".

 

[6StringJazzer]

To be able to provide actual VBA code for this, we'll need more detail.

The code should loop thru a column looking for a string.

What string is it looking for?

Depending on the string, if it is found it should set a value in one or two different columns

What tells us whether to set a value in one or two columns for which strings?

a abbrevitaion has been used.

Do you have a standard list of abbreviations for each possible string? There is no standard for how to abbreviate words so the code can't guess.
abbreviation: abb
international: intl
quarter: qtr

 

[Brutusar]

Hi, no I do not have a list yet, it can be many. I will also have to add in new ones as there may turn up new ones. So for demo purposes I just used the Abbreviation ones.

 

[6StringJazzer]

I would have one loop. Each pass through the loop uses the Find method for the range of the column where you have your text. You would use Find once for the word and once again for each abbreviation. Then when found you would lookup which columns you are supposed to set data in for that term, then set it.

But your description is too generic to actually give you real code that would work in your file.

 

[Brutusar]

I will try to improve my explanation. ( I would probably to have survived very long as a teacher ). See the new attached screenshot.

In this case there would be a array, or maybe two is necessary. In the example I have used two. Array 1: (Miami, New York, Chicago, Sydney). If these are found it should insert an x in col B and mark the row green.
Array 2: (USA, Australia). If one or more of these are found a x should be inserted to Col C and mark the row green.

Then some RegEx arrays used in the same way for finding any abbreviations, like for example N.Y. ( Don't worry about the regex itself, that I can figure out, but how and where should it be used is the important question) If the code in the regex finds a match the correct column should me marked with an x, but the background color set to yellow so it easy to find and check manually.