Msgbox if certain value exists in range

[Crispy85]

Hi all,

It isn't probably to difficult, but i'm still a vba noob

In column P of my sheet there are text values. Sometimes, for example the value could contain "test".
If this is the case i would like that cell to be colored red, and also a msgbox that says "Test is found. It occurs amount of times" where it counts the amount of times it occurs also.

Who wants to help me?

Thanks!!!

[shiftyspina]

Firstly use conditional formatting, so use a formula in conditional formatting to say

Please Login or Register  to view this content.

then you can use the fill in the conditional formatting options to fill it red, you can then drag this down to the end of your column as long as you don't lock in the cell i.e $P$5

For counting, there may be an easier way, but I would put a helper cell and use

Please Login or Register  to view this content.

This will return how many times "Test" appears, from this answer you can then run a message box to display how many times it appears using the following, for example if your helper cell is Q1

Please Login or Register  to view this content.

To then run this crate a command button in excel under the developer tab, and place the code above in the command button, when you click the button the message box will display

[Crispy85]

Hi Shifty,

Thanks you but i know how conditional formatting works, and also how the formulas work.
However, i want it included in a vba script.

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.

[Crispy85]

Hi John,

Thanks!! i think this might actually work but my idea was that the cell text would be "this sentence contains test" and the script should search "test". Is it as simple as changing

Please Login or Register  to view this content.

?

So to be more clear.
Test can be found as a value within a longer value. Like search "test" in "this cell has the word test". In this case the script should also color the cell and count it.

[JOHN H. DAVIS]

Try:

Please Login or Register  to view this content.

[Crispy85]

Hi John,

Thanks! This is very strange.
"test" is just because of that i don't want to use company information. But whenever i create a sample file with "test" the macro runs perfectly.
However, if i put it in the final file. I use "cost" as a value and it only colors the first result red and at the and says "it occurs 0 times".

Do you have an idea?

[Crispy85]

I attached a sample file for the word "cost".
When i run it, it only counts 0 and only colors the first result.

[JOHN H. DAVIS]

Try this. Note changes - 1) Moved code to a standard module vrs the Sheet1 module, 2) Added Option Compare Text at the top.

[Crispy85]

Great John, Thanks!!

[JOHN H. DAVIS]

You're welcome. Glad to help out and thanks for the feedback.