On Sun, 11 Dec 2005 09:09:01 -0800, "Jimbola"
wrote:

Sorry i'm not being clear. What i want if for the criteria for select case to
reside in a cell. So for example in the code you gave instead of habing

Case is <20

i want something like

case range("A30")

and for the criteria e.g. <20 to reisde in A30 so it can be changed on the
sheet rather the code. In a similar way that i have the colour picked up from
the work sheet in my original code by using offset.

OK, then something like this:

=========================================
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim expressionlist() As Long
Dim c As Range
Dim i As Long
Dim ExpressionListRange As Range

Set ExpressionListRange = [g1:g3]
ReDim expressionlist(ExpressionListRange.Count)

For Each c In ExpressionListRange
expressionlist(i) = c.Value
i = i + 1
Next c

Select Case Range("F1")
Case Is < expressionlist(0)
Range("A1").Interior.Color = vbRed
Case Is < expressionlist(1)
Range("A1").Interior.Color = vbGreen
Case Is < expressionlist(2)
Range("A1").Interior.Color = vbYellow
Case Else
Range("A1").Interior.Color = vbBlue
End Select
End Sub
==========================

or

==================================
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim c As Range

Select Case Range("F1")
Case Is < Range("g1").Value
Range("A1").Interior.Color = vbRed
Case Is < Range("g2").Value
Range("A1").Interior.Color = vbGreen
Case Is < Range("g3").Value
Range("A1").Interior.Color = vbYellow
Case Else
Range("A1").Interior.Color = vbBlue
End Select
End Sub
===================================


--ron