Display VBA msgbox when cell contains text

[histoman52]

Hello, apologies for any errors this is my first post and I am relatively new to VBA.

I work in a lab and we use a spreadsheet for recording some tests that need to be sent away. I need a msgbox to appear when a specific test is recorded "HER2" to remind the user to check if another test is also required. I have managed to do this, however only if the cell contains exactly "HER2" or "her2" and the cell may not always contain exactly this as there are a few variations of how it is entered (Gastric HER2, HER-2, Gas HER2 etc). Is there a way to have this recognise the HER bit even if it contains other text as this will always be included and trigger the msgbox.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim xCell As Range, Rg As Range
    On Error Resume Next
    Set Rg = Application.Intersect(Target, Range("C1:C9999"))
    If Not Rg Is Nothing Then
        For Each xCell In Rg
            If UCase(xCell.Value) = "HER2" Then
                MsgBox "Gastric HER2 requested, check with requestor if MMR required"
                Exit Sub
            End If
        Next
   End If
End Sub

Thanks

 

[NoSparks]

try

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim xCell As Range, Rg As Range
    On Error Resume Next
    Set Rg = Application.Intersect(Target, Range("C1:C9999"))
    If Not Rg Is Nothing Then
        For Each xCell In Rg
            'If UCase(xCell.Value) = "HER2" Then
            If InStr(1, UCase(xCell.Value), "HER") > 0 Then
                MsgBox "Gastric HER2 requested, check with requestor if MMR required"
                Exit Sub
            End If
        Next
   End If
End Sub