VBA based conditional formatting (>3 formats needed)

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