sorry nope, its the actual criteria 50 that i want in cell A30, so it can be
changed on the sheet rather than the code.

thanks

"Gary Keramidas" wrote:

not sure if this is what you want, and i don't have your range, but it will
set cell A3 to the color depending on the value in A30

Option Explicit
Dim cell As Range

Sub test()
Set cell = Range("A3")
With cell
Select Case Range("a30").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 With
End Sub

--


Gary


"Jimbola" wrote in message
...
Sorry Bob I don't get this, it doesn't work. Thanks anyway
Let me re-explain the problem i am having, my original code works fine,
but
for the first test rather than put the criteria in the code i would like
it
in A30 as it can change.

"Bob Phillips" wrote:

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

something like this?

Case Is 50
.Interior.ColorIndex = Range("A30").Value

then when a30 is change the colorindex changes
--


Gary


"Jimbola" wrote in message
...
sorry nope, its the actual criteria 50 that i want in cell A30, so it can
be
changed on the sheet rather than the code.

thanks

"Gary Keramidas" wrote:

not sure if this is what you want, and i don't have your range, but it
will
set cell A3 to the color depending on the value in A30

Option Explicit
Dim cell As Range

Sub test()
Set cell = Range("A3")
With cell
Select Case Range("a30").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 With
End Sub

--


Gary


"Jimbola" wrote in message
...
Sorry Bob I don't get this, it doesn't work. Thanks anyway
Let me re-explain the problem i am having, my original code works fine,
but
for the first test rather than put the criteria in the code i would
like
it
in A30 as it can change.

"Bob Phillips" wrote:

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

sorry no, more like

case range("A30") where A30 can contain the criteria
(i.e 50)
..Interior.ColorIndex = Range("Condition").Offset(1, 2) < this works OK
not the problem

have just a number in A30 is can deal with but have the operand = <
and so on i can't figure




"Gary Keramidas" wrote:

something like this?

Case Is 50
.Interior.ColorIndex = Range("A30").Value

then when a30 is change the colorindex changes
--


Gary


"Jimbola" wrote in message
...
sorry nope, its the actual criteria 50 that i want in cell A30, so it can
be
changed on the sheet rather than the code.

thanks

"Gary Keramidas" wrote:

not sure if this is what you want, and i don't have your range, but it
will
set cell A3 to the color depending on the value in A30

Option Explicit
Dim cell As Range

Sub test()
Set cell = Range("A3")
With cell
Select Case Range("a30").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 With
End Sub

--


Gary


"Jimbola" wrote in message
...
Sorry Bob I don't get this, it doesn't work. Thanks anyway
Let me re-explain the problem i am having, my original code works fine,
but
for the first test rather than put the criteria in the code i would
like
it
in A30 as it can change.

"Bob Phillips" wrote:

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

another guess

c = Range("a30").Value

Case Is c
.Interior.ColorIndex = Range("Condition").Offset(1, 2)

--


Gary


"Jimbola" wrote in message
...
sorry no, more like

case range("A30") where A30 can contain the
criteria
(i.e 50)
.Interior.ColorIndex = Range("Condition").Offset(1, 2) < this works
OK
not the problem

have just a number in A30 is can deal with but have the operand = <
and so on i can't figure




"Gary Keramidas" wrote:

something like this?

Case Is 50
.Interior.ColorIndex = Range("A30").Value

then when a30 is change the colorindex changes
--


Gary


"Jimbola" wrote in message
...
sorry nope, its the actual criteria 50 that i want in cell A30, so it
can
be
changed on the sheet rather than the code.

thanks

"Gary Keramidas" wrote:

not sure if this is what you want, and i don't have your range, but it
will
set cell A3 to the color depending on the value in A30

Option Explicit
Dim cell As Range

Sub test()
Set cell = Range("A3")
With cell
Select Case Range("a30").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 With
End Sub

--


Gary


"Jimbola" wrote in message
...
Sorry Bob I don't get this, it doesn't work. Thanks anyway
Let me re-explain the problem i am having, my original code works
fine,
but
for the first test rather than put the criteria in the code i would
like
it
in A30 as it can change.

"Bob Phillips" wrote:

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