Below is some code I use to color some cells based on text entered into the
cell, and it works fine. If the word Vacation is entered that cell and the
one above it is changed to the color blue. But when the word is removed,
only the cell with the word is changed back to no color.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim icolor As Integer

If Not Intersect(Target, Range("A40:G57")) Is Nothing Then

Select Case Left(Target.Text, 4)
Case Is = "Sick"
icolor = 38
Target.Offset(-1, 0).Resize(1, 1).Interior.ColorIndex = icolor
End Select

Select Case Left(Target.Text, 4)
Case Is = "Vaca"
icolor = 34
Target.Offset(-1, 0).Resize(1, 1).Interior.ColorIndex = icolor
End Select

End Select

Target.Interior.ColorIndex = icolor
End If

End Sub