Use the calculate event and cycle through each cell in the range.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Ram B" wrote in message
...
I have added a VB script to a sheet to change color of the cell based on
input
Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer

If Not Intersect(Target, Range("F1:F510")) Is Nothing Then
Select Case Target
Case "Red"
icolor = 3
Case "Green"
icolor = 4
Case "Blue"
icolor = 5
Case "White"
icolor = 2
Case "Gray"
icolor = 15
Case ""
icolor = 0
Case Else
'Whatever
End Select

Target.Interior.ColorIndex = icolor
Target.Font.ColorIndex = icolor

End If

End Sub
---------------------------------------------------
This works well if the data is entered manually.
some cells that have a calculated input using a formula does not get
updated. It works if I use "Conditional Formatting" but the limitation
there
is 3 colours. Any help will be appreciated.