Conditional Format- Refresh   

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.

date: Tue, 1 Jul 2008 19:52:01 -0700   author:   Ram B

Re: Conditional Format- Refresh   

As this is a question about Excel, you should ask in the 
microsoft.public.excel.programming newsgroup.

-- 
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

"Ram B"  wrote in message 
news:77E9D940-8C14-4C5C-9A64-9BB727AD7454@microsoft.com...
>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.

date: Wed, 2 Jul 2008 16:44:52 +1000   author:   Doug Robbins - Word MVP