Selection Change macro to OFFSET

[MannStewart]

Hello there,


1. I'm trying to write a Selection Change macro to OFFSET the activecell to different cells (columns-Q,S,T) on each row based on the Text(s) contained in the user input cell in column-O. But my code doesn't seem to work. I attached a image below to illustrate my situation, with remarks that specify the criteria needed for how the specified keywords must be found in column-O, including the large/small caps or not they appear, in order for the OFFSET to move. Data in my workbook is from row 5 through to 5000.


SelChngOFFSET.PNG


This the last method i used in the most recent attempt at making the code to achieve above, all have failed. I also experimented with InStr & that failed miserably.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

   If Not Intersect(Target, Range("$O$5:$O$5000")) Is Nothing And Target.Value <> "" And Target.Cells.Count = 1 Then

    Range("$A$5:$A$5000").Calculate

     If Range("A" & Target.Row) = "REC" Then
      With ActiveCell
     .Offset(0, 4).Select
      End With
      
     ElseIf Range("A" & Target.Row) = "PWG" Then
      With ActiveCell
     .Offset(0, 2).Select
      End With
   
   End If
   
End Sub

2. The second problem I have is with column-A.
I use the ISNUMBER function for column-A to return a short ID based on the text string in column-O, all is well till when the keywords in O is more than one keyword to locate. When the Description string in column-O is only 1 keyword to find, say the keyword to lookout for is only "RECYCLE", the ISNUMBER returns "REC" without any error. But i dont know how to use ISNUMBER to also lookout for & cover all the other possibilities shown in above image, ie. when ID should lookout for in the input cell strings that might contain 2 or more keywords simultaneously, if it meets that possibility it can't correctly return the correct ID.

Here is my effort ISNUMBER example of the failed function for more than 1 keyword to return an ID, example when I need the cell in A5 to return "PWG" if O5 contains ALL 3 keywords "Return","to","via", but it anyhow returns "PWG" even if the user has only typed "Return" withoput typing "to" and "via"

=IF(ISNUMBER(SEARCH({"Return","to","via"},O5)),"PWG",IF(ISNUMBER(SEARCH("Processing*",O5)),"PWG","")

Hope someone can help me with above 2 issues,

Stewart.