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.
Select Case Target.Value
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 Sub



--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Jimbola" wrote in message
...
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