VBA based conditional formatting (>3 formats needed)

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??

Mudraker: That works! Cheers mate.

Roy: I left out some other formats for the sake of shortening the code to pasted here. I'm actually using 7 different formats.

I fixed the quotes and it was still throwing errors so I poked around in the debugger and realized Rng was null until inside the for loop. So I moved that line of code. Here's the code that works for me.

Private Sub Worksheet_Change(ByVal Target As Range)
   
   Dim Rng As Range
   Dim RowRng As Range
   Dim iColour As Integer
 
   For Each Rng In Target
      
      Set RowRng = Range(Rng.Offset(0, -9), Rng)
      
      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