Thanks Bob, I didn't catch that. I'll read closer next time.

Alan


"Bob Phillips" wrote:

Not 3 colours, 3 conditions.

--
HTH

Bob

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

"Alan" wrote in message
...
The reason it does not change the cell with the formula is because it is
not
the "target" cell, but is only affected by the "target" cell.

I don't know what version you are running. I am running 2002. In 2002
Conditional Formatting has all the Excel standard colors, not just 3.

Format
Conditional Formatting
Click the Format button
Click the Pattern tab.
All Excel standard colors are available here.

Conditional Formatting is the easiest way to go. You could calculate each
cell in the range after a change occurs.


Alan



"Ram B" wrote:

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.