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


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