create pop-up message once if substring entered within a range

[KBDund]

Hello. I have been trying to develop a simple code in Excel 2007 that creates a pop-up message when any cell within a range (i.e. D13:D25) contains the substring "ggg". I have tried to do this indirectly with a formula in another cell i.e. G13 (=IF(ISERROR(SEARCH("g100",D13,1) ),"", 1) and having the code look in G13 for the value 1 to activate the popup window. This seems indirect and only works for the first cell in the range, not the whole range. Additionally, the message keeps appearing every time another cell has text entered which is not desired. Any thoughts? Thanks in advance.

 

[KrisSquare]

Set some color code to the cell for which the message has already popped up and ignore that cell from next time.

Please post your code

 

[KBDund]

Sorry I am not sure I follow that. The code is embarrassingly simple:

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("G13") = 1 Then
MsgBox "Whatever!"
End If
End Sub

Basically the user would open the file and is expected to type one or more strings into a range - if ggg appears then a message would appear indicating what they might consider entering in subsequent cells. Sorry if this is not making much sense...

 

[KrisSquare]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)

'restricting the code to work with the changes to Range D13:D25
If Target.Column = 4 And Target.Row >= 13 And Target.Row <= 25 Then

For Each c In Range("G13:G25").Cells

If c.Value = 1 And c.Interior.ColorIndex <> 15 Then
MsgBox "Whatever!"
c.Interior.ColorIndex = 15

ElseIf c.Value = 0 Then
c.Interior.ColorIndex = 2
End If

Next
End If
End Sub

 

[KBDund]

Thanks. Unfortunately this is not achieving the desired effect. The range I am working with is D13:D25 but I have set the cell up with the formula that is looking for "ggg" in D13:D25 is another cell where the user will not be entering data. I could change the line in your code:

For Each c In Range("G13:G25").Cells, to:

For Each c In Range("D13:D25").Cell, and

If c.Value = 1 And c.Interior.ColorIndex <> 15 Then, to:

If c.Value = "ggg" And c.Interior.ColorIndex <> 15 Then

and I will get the desired effect if I type "ggg" into any of the cells in the range D13:D25 but this only works for the string ggg and not as a substring within the cell. If there is a way to modify the revised code to have it look for the ggg substring we would have it (I think). It would be the equivalent of the search function in excel. Thanks again.

 

[KBDund]

Any thoughts?

 

[KrisSquare]

That is what is being done in G13:G25 as per your original message

In G13:g25 add this formula =IF(ISERROR(SEARCH("ggg",D13,1) ),"", 1) and use my original code

it should work

 

[KBDund]

Great - thanks very much.