Cell coloring problem

[Patrick Simonds]

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

 

[Guest]

Try it like this. Strictly speaking the Case Else statement may not be needed
as icolor will be 0 by default if not set to anything but I have put it in
for clarity.

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
Case Is = "Vaca"
icolor = 34
Case Else
icolor = 0
End Select

Target.Offset(-1, 0).Interior.ColorIndex = icolor
Target.Interior.ColorIndex = icolor
End If
End Sub

Hope this helps
Rowan

 

[Bob Phillips]

Where is the code that sets it to no colour in that?

 

[Carl]

Colors are a funny thing. They adjust to the users pallet. my index 34 may
not be you r ondex 34. you may want to check out RGB settings. In the
meantime:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:G57")) Is Nothing Then

Select Case UCase(Left(Target.Text, 4))
Case Is = "SICK"
Target.Offset(-1, 0).Resize(1, 1).Interior.ColorIndex = 38
Case Is = "VACA"
Target.Offset(-1, 0).Resize(1, 1).Interior.ColorIndex = 34
Case Is = ""
Target.Offset(-1, 0).Resize(1, 1).Interior.ColorIndex =
xlColorIndexNone
End Select
End If
End Sub

HTH
me