Thanks for that, the macro bit works fine, the only issue i have is tha
the values 1-7 in column s are the resultant of a formula that assign
the ranking. as such the colours do not update when the value of th
cell changes. it does work when i manually enter a value so i know th
code works.

is there a simple way around this???

help once again would be greatly appreciated.



Bob Phillips Wrote:
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myColor As Long

If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("S:S")) Is Nothing Then Exit Sub

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

--

HTH

RP



--
nejlangto
-----------------------------------------------------------------------
nejlangton's Profile: http://www.excelforum.com/member.php...fo&userid=1397
View this thread: http://www.excelforum.com/showthread.php?threadid=26428

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



--

HTH

RP

"nejlangton" wrote in message
...

Thanks for that, the macro bit works fine, the only issue i have is that
the values 1-7 in column s are the resultant of a formula that assigns
the ranking. as such the colours do not update when the value of the
cell changes. it does work when i manually enter a value so i know the
code works.

is there a simple way around this???

help once again would be greatly appreciated.



Bob Phillips Wrote:
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myColor As Long

If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("S:S")) Is Nothing Then Exit Sub

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

--

HTH

RP




--
nejlangton
------------------------------------------------------------------------
nejlangton's Profile:
http://www.excelforum.com/member.php...o&userid=13970
View this thread: http://www.excelforum.com/showthread...hreadid=264289