N
nejlangton
Thanks Bob,
That works pefectly!
Nejl
That works pefectly!
Nejl
Bob said:Try this variation, it should cater for formulae and direct entry
Private Sub Worksheet_Calculate()
Dim cell As Range
For Each cell In Range("S:S")
If Not IsEmpty(cell.Value) Then
UpdateColour cell
End If
Next cell
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("S:S")) Is Nothing Then Exit Sub
UpdateColour Target
End Sub
Private Sub UpdateColour(ByVal Target As Range)
Select Case LCase(Target.Value)
Case Is = "1": Cells(Target.Row, "B").Interior.ColorIndex = 3
Case Is = "2": Cells(Target.Row, "B").Interior.ColorIndex = 46
Case Is = "3": Cells(Target.Row, "B").Interior.ColorIndex = 6
Case Is = "4": Cells(Target.Row, "B").Interior.ColorIndex = 4
Case Is = "5": Cells(Target.Row, "B").Interior.ColorIndex = 34
Case Else:
Cells(Target.Row, "B").Interior.ColorIndex = xlColorIndexNone
End Select
End Sub