clear if blank

[Trevor3007]

hi & hope this finds you well?

I am using the following VBA, but although it inserts the applicable colour, if you then make that cel blank, the colour remains. Can soem kind person pleas insert the applciable for me?

Private Sub Worksheet_Change(ByVal Target As Range)


Application.ScreenUpdating = False
    Dim rng As Range
      
    For Each rng In Range("e9:e39:b200")
        Select Case rng.Value
            Case "Off"
                With Range("A" & rng.Row).Resize(1, 7)
                    .Interior.ColorIndex = 36
                    .Font.Bold = True
                End With
               
            Case "off"
                With Range("A" & rng.Row).Resize(1, 7)
                    .Interior.ColorIndex = 36
                    .Font.Bold = True
                End With
        End Select
    Next rng
   
      
    Application.ScreenUpdating = True


If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub



    On Error Resume Next

    If Not Intersect(Target, Range("jb6:jb18")) Is Nothing Then

        Application.EnableEvents = False

        Target = UCase(Target)

        Application.EnableEvents = True

    End If
     

    On Error GoTo 0


If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub



    On Error Resume Next

    If Not Intersect(Target, Range("e6:e40")) Is Nothing Then

        Application.EnableEvents = False

        Target = StrConv(Target, vbProperCase)

        Application.EnableEvents = True

    End If

    On Error GoTo 0

     
   
 
End Sub

Many thanks in advance,

 

[Fluff]

How about

    For Each rng In Range("B9:E200")
        If LCase(rng.Value) = "off" Then
            With Range("A" & rng.Row).Resize(1, 7)
                .Interior.ColorIndex = 36
                .Font.Bold = True
            End With
         ElseIf rng.Value = "" Then
            With Range("A" & rng.Row).Resize(1, 7)
                .Interior.ColorIndex = xlNone
                .Font.Bold = False
            End With
        End If
    Next rng

 

[Trevor3007]

How about

    For Each rng In Range("B9:E200")
        If LCase(rng.Value) = "off" Then
            With Range("A" & rng.Row).Resize(1, 7)
                .Interior.ColorIndex = 36
                .Font.Bold = True
            End With
         ElseIf rng.Value = "" Then
            With Range("A" & rng.Row).Resize(1, 7)
                .Interior.ColorIndex = xlNone
                .Font.Bold = False
            End With
        End If
    Next rng

hi fluff,

sir, you are a genius... thank you :}

 

[Fluff]

You're welcome & thanks for the feedback.