Sorry Mike thats unclear!, i meant with my first select case[vba]Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.value
Case Is = "Sick"
Target.Interior.ColorIndex = 4 'Green
Case Is = "KL"
Target.Interior.ColorIndex = 3 'yellow
Case Is = "DIL"
Target.Interior.ColorIndex = 7 'Purple
Case Is = "OFF"
Target.Interior.ColorIndex = 5 'Blue
End Select
End Sub
[/vba]which was before i added specialcells, it is that which wouldn't work with data validation but any version of xl later than 2000 should trigger a change event.