Good Morning/Afternoon/Evening

I'm looking to conditionally format the cell colour of an entire row based on text entered in the last cell of the row. Excel's built-in conditional formatting is beautiful for this but I need six different formats (and three seems to be the max).

I did a search and found this helpful thread and I'm trying to adapt the code to my needs.

Here's what I've got so far. 

Private Sub Worksheet_Change(ByVal Target As Range)

   Dim Rng As Range
   Dim RowRng As Range
   Dim iColour As Integer
   
   MsgBox "Macro has been executed!!!!"

   Set RowRng = Range(Rng, Range("Rng").Offset(0, -9))
   
   For Each Rng In Target
      
      If Not Application.Intersect(Rng, Range("J1:J100")) Is Nothing Then
         Application.EnableEvents = False
         Select Case Rng.Value
             Case Is = "ahead"
                iColour = 3
             Case Is = "on time"
                iColour = 45
             Case Is = "behind"
                iColour = 50
             Case Else
                iColour = 0
         End Select
         If iColour = 0 Then
            RowRng.Interior.ColorIndex = xlNone
         Else
            RowRng.Interior.ColorIndex = iColour
         End If
      End If
   Next Rng
   Application.EnableEvents = True
End Sub
At one point in time, when I had set RowRng to a static range, the macro would successfully change the row colour whenever I changed a cell in J1:J100.

I then set out to modify my code to change only the row of the selected cell in the J column and came up with this part.

Set RowRng = Range(Rng, Range("Rng").Offset(0, -9))
I was getting an error at this line, but now all of a sudden for some reason, the macro isn't even getting executed when I enter text in the J column of the workseet. I know this because it isn't showing my message box when I change a value in the J column.

What gives??