VBA to evaluate if a certain letter exists in string

[Burrgogi]

I have a very simple but very repetitive task that I would like some help on.
I have a word that gets entered into sheet1, column A. It's a rolling list of words so today the last row is 50 but tomorrow the last row will be 51... the day after that will be row 52, etc,.
Whatever is entered there, I would like the macro to store that as a string variable and do the following:

1) Does the word end in 'Y?
If yes, then search for the word in ThisWorkbook.Sheets("Ends with Y") and mark that cell in bold font.
Otherwise, produce a message box indicating that the word was not found and exit sub

2) Does the word contain 'Y' anywhere in the string? EXCEPT for the last letter
If yes, then search for the word in ThisWorkbook.Sheets("Contains Y") and mark that cell in bold font.
Otherwise, produce a message box indicating that the word was not found and exit sub

There will be some cases where both conditions are met such as YUMMY in which case the search should be done in ThisWorkbook.Sheets("Ends with Y").

It's very simple to have the VBA macro to grab the last letter, using something like this: MyKeyWord_lastLetter = Right(c2, 1)

The problem is that the length of the word will always vary and that particular solution does not address 'Y' occurring in other parts of the string (beginning, middle, etc).

 

[dave3009]

Is it something like this?

This goes in the sheet code for the sheet you enter then values into, adjust the names etc to suit.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim tWord As String
Dim Found As Range

If Not Intersect(Target, Range("A:A")) Is Nothing Then
    tWord = UCase(Target.Value)
      
    If Right(tWord, 1) = "Y" Then
        ' Ends Y
        Set Found = Sheets("End Y").Range("A:A").Find(Target.Value, Range("A1"))
        If Not Found Is Nothing Then Found.Font.Bold = True
    ElseIf InStrRev(Left(tWord, Len(tWord) - 1), "Y") Then
        ' Contains Y
        Set Found = Sheets("Contains Y").Range("A:A").Find(Target.Value, Range("A1"))
        If Not Found Is Nothing Then Found.Font.Bold = True
    Else
        MsgBox "No Y"
    End If
    
End If

End Sub

 

[Burrgogi]

I customized a message box to read "Today's key word does not contain any Y"

It's not working at all. I tested by using a word like Happy and it's not detecting the Y at the end.

 

[Fuji]

Not clear about case sensitivity...

Case sensitive and find Exact match.
So only find capital "Y", not "y" in the string.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim s, wsName$, r As Range
    If Intersect(Target, Columns("a")) Is Nothing Then Exit Sub
    s = Target.Value
    If Not s Like "*Y*" Then MsgBox "Today's key word does not contain any Y": Exit Sub
    wsName = IIf(s Like "*Y", "Ends with ", "Contains ") & "Y"
    Set r = Sheets(wsName).Cells.Find(s, , -4163, 1, , , True, False, False)
    If Not r Is Nothing Then
        r.Font.Bold = True
        Target.Font.Bold = True
    Else
        MsgBox """" & s & """ Is Not found in """ & wsName & """"
    End If
End Sub

If case insensitive
1) change 2 Y in bold to [Yy]
2) change True to False

 

[dave3009]

I customized a message box to read "Today's key word does not contain any Y"

It's not working at all. I tested by using a word like Happy and it's not detecting the Y at the end.

How did you implement it? In what module did you place the code?