Sorry that code I posted needs to be a little more fool proof... Try this...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer
Dim rng As Range
On Error Resume Next
Set rng = Intersect(Target.Dependents, Range("Table"))
On Error GoTo 0
If rng Is Nothing Then Exit Sub

Select Case rng(1, 1)
Case 1 To 5
icolor = 5
Case 6 To 10
icolor = 6
Case 11 To 15
icolor = 46
Case 16 To 20
icolor = 3
Case 21 To 25
icolor = 13
Case Else
'Whatever
End Select

rng.Interior.ColorIndex = icolor

End Sub
--
HTH...

Jim Thomlinson


"Jim Thomlinson" wrote:

I am assuming that you want to change the colour when the value of the
formula changes. In order to do that try checking the dependants of the cell
that is being changed... The the dependant is the formula in your range then
chenge the colour of the dependant cell. Something like this...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer

If Not Intersect(Target.Dependents, Range("Table")) Is Nothing Then
Select Case Target.Dependents(1, 1)
Case 1 To 5
icolor = 5
Case 6 To 10
icolor = 6
Case 11 To 15
icolor = 46
Case 16 To 20
icolor = 3
Case 21 To 25
icolor = 13
Case Else
'Whatever
End Select

Target.Dependents.Interior.ColorIndex = icolor
End If
End Sub

--
HTH...

Jim Thomlinson


"BC" wrote:

Hi,

I have two columns in my worksheet which are used as lookup input
parameters. A results column is populated with the value returned from this
look up. The cells of the result column will change color based on the value
returned from the lookup. I have used the code shown below to acheive this.
This works perfectly when the look up is performed for the first time.
However, if a user changes one of the parameters in the worksheet, the lookup
will be performed - the correct value is returned to the results column but
the color does not change. Does anyone know how I can get the color to change
dynamically - i'm sure its some flaw in my code.

Thanks a lot guys

BC

Conditional Formatting Code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer

If Not Intersect(Target, Range("Table")) Is Nothing Then
Select Case Target(1, 1)
Case 1 To 5
icolor = 5
Case 6 To 10
icolor = 6
Case 11 To 15
icolor = 46
Case 16 To 20
icolor = 3
Case 21 To 25
icolor = 13
Case Else
'Whatever
End Select

Target.Interior.ColorIndex = icolor
End If

End Sub