Display Message Box When cell(s) contain a certain text in a column

[Kimston]

Greetings everyone,
I am trying to display a message box when a workbook opens if column B contains the word "term". With the use of the below code; however, I am getting a Run-time error '1004' Method 'Range' of object' 'Global' failed. I would appreciate if someone can tell me what I am doing wrong.

Please Login or Register  to view this content.

[Andrew-R]

You need to specify which sheet the range object refers to, e.g.

Please Login or Register  to view this content.

Also, there's no point doing an intersect with Target - there isn't a range called Target in this sub, so the intersection will always return nothing.

Also, also, is rng meant to refer to a single cell (Range("B2:B2")) or a whole column (Range("B:B"))? What you've got at the moment is an invalid combination of the two. If it's meant to be multiple cells then you can't evaluate all of the values using rng = "term", you'll have to use a .Find.

[Kimston]

Thank you Mr. Andrew-R for your feedback. this is what I have done and so far it works. However, is there a way I can add to display the cell location where the word "term" appears? Here is my code

Please Login or Register  to view this content.

[Richard Buttrey]

Hi,

I don't know why you think the code is working as I think you expect it to work since as Andrew has intimated you will always see the message box because you are not testing or searching for anything.

I a situation like this I always prefer to use a cell on the worksheet which evaluates the condition you are testing for, and then if necessary use a macro to report the value of the cell.

So for instance if you are looking for the occurrence of the word 'term' in column B then in a cell say A1 enter

Please Login or Register  to view this content.

this will return the cell address of the first occurrence of 'term'. You could conditionally format this cell so that it stands out with say a red background if it's not equal to 'Term not found', which may be sufficient for you. If you really need a macro then use something like

Please Login or Register  to view this content.

[Andrew-R]

You can modify your code slightly to display the first cell containing "term":

Please Login or Register  to view this content.

Changing it to report multiple matches will involve looping through all of the matches and might produce a very long message box.