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.

"Ron Rosenfeld" wrote:

On Sun, 11 Dec 2005 07:59:02 -0800, "Jimbola"
wrote:

Hello,

I've written this code to get round the conditional format limit.
The first case tests if the cell 50, but instead of changing the code
i want the cell A30 to hold the criteria as it can change. But using Case
range("A30") doesn't work. Can someone please point me in the write
direction

Not sure what you mean by "doesn't work". But the following code work fine
using similar logic. If you could define, more clearly, what "doesn't work"
means ...

=====================
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Select Case Range("F1")
Case Is < 20
Range("A1").Interior.Color = vbRed
Case Is < 50
Range("A1").Interior.Color = vbGreen
Case Else
Range("A1").Interior.Color = vbBlue
End Select
End Sub
======================






Private Sub Worksheet_Change(ByVal Target As Range)
' This macro is activated everytime a change is made to
' this worksheet (TestArea)
Dim r As Range
Dim cell As Range

'Set the range for the formatting
Set r = Intersect(Range("shtRange"), Target)

' If the change in the worksheet is not in the
' tested range, exit the macro.
If r Is Nothing Then Exit Sub

'Change to formatting of the cell that changed.
For Each cell In Range("BoxRange")
With cell
Select Case cell
Case Is 50 <<<<<<<<<<<<<<<<<
.Interior.ColorIndex = Range("Condition").Offset(1, 2)
Case 2
.Interior.ColorIndex = 2
Case 3
.Interior.ColorIndex = 3
Case 4
.Interior.ColorIndex = 4
Case 5
.Interior.ColorIndex = 5
Case 6
.Interior.ColorIndex = 6
Case 7
.Interior.ColorIndex = 7
Case 8
.Interior.ColorIndex = 8
Case 9
.Interior.ColorIndex = 9
Case 10
.Interior.ColorIndex = 10
Case Else
.Interior.ColorIndex = 0

End Select

End With
Next

End Sub


Many thanks
J

--ron